Very large database file > 2TB shrinking

  • fixfox8


    Points: 488

    GilaMonster (8/15/2008)

    Ok. Which one are you trying to shrink and what size are you trying to shrink it to?

    Sop -1 2367438

    Sop_log 12800000 1

    Sop_1 192000000 1000000

    Sop_2 192000000 1000000

    Sop_3 192000000 1000000

    Sop_4 192000000 512000

    First one to 1 terabyte. If it is not possible to something less then two at least. Could you please explain what is the problem.Thanks.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Odd. I suspected it was a problem with the file's max_size setting, but apparently not.

    Grasping at straws here... what's the command that you're using to shrink the file?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104


    How much free space you have in the file?

    I suspect that you are trying to shrink the file to a size where your data is holding larger space.

    If that is your case, you need to move some tables into different fielgroups and then shrink the database

    You can also think of partitioning some large tables (and place the partitions in different file groups)

    SQL Server 2005, came up with this nice feature and it is really a very good method to do. You can move the data chunk by chunk during offpeack hours.

    Prithiviraj Kulasingham

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720511

    I assume you have free space on the drive, correct? Also, is there free space listed in the database?

  • fixfox8


    Points: 488

    Thanks all. I have solved the problem. Just I am curious, if I have .mdf and some .ndf files in the Primary filegroupe, Is it possible some how to move a part of the data from .mdf to any .ndf/ and then I can shrink the .mdf or re size it. Best Regards.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720511

    any update on the issue or solution? Might help the next person.

  • SequelDBA

    Ten Centuries

    Points: 1093

    I have a similar problem. I have a database that was restored from Prod to Test. The DB has multiple secondary files. We have purged prod data from this copy and now I'm trying to complete a shrink, as well as use only one primary data file. There's still over 200 GB that I can not reclaim. When I run a shrink (DBCC SHRINKFILE (Testdatafile, EMPTYFILE) I receive this error:

    Msg 2555, Level 16, State 2, Line 1

    Cannot move all contents of file "Testdatafile" to other places to complete the emptyfile operation.

    Here are the results of sp_spaceused:

    Database Database Size Unallocated

    TestDB1 235923.63 MB 226780.71 MB

    Reserved Data Index Size Unused

    1375528 KB 1062424 KB 269432 KB 43672 KB

    Thank you...


Viewing 7 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply