After we did massive truncate of data, there is a huge unused space in multiple databases data files, and I wonder what would be the best way of releasing it back. I know most of the experts are against it, I am not talking about single digit GB space, its more 56% unused space out of 800GB data files, and there are many more files in the server.

SQL Version - 2014 Standard ( this cannot be changed, and no more storage can be added)

I know the below options could work, but also potentially cause fragmentation.

  1. DBCC SHRINK (causes massive fragmentation and uses back some space while update status).
  2. Moving the Primary File Group table to Secondary File Group.
  3. Adhoc shrink in Chunks (this also causes fragmentation).

Any suggestions above this and new ideas are greatly appreciated.

  • 3
    Obligatory first question: why do you need to release it back? – LowlyDBA Nov 15 '17 at 21:34
  • 1
    Shrink then defragment – paparazzo Nov 15 '17 at 21:52
  • 1
    @Paparazzi ...which will grow the file(s) again, at least to some degree. (Not a complaint, just a disclaimer. There's no perfect answer.) – Aaron Bertrand Nov 15 '17 at 23:34
  • What kind of fragmentation do you expect which is not already existing? – eckes Nov 16 '17 at 6:53
  • So What I did today was something I did not expect to do, a huge task cause I did some research work over the databases about having ambiguous data, and YES I found that data in multiple places carry and restoring the same database multiple times with multiple names, so simply asked to drop the datasets which are not required and have to follow normalized pattern of data, hence they agreed and dropped tons of tables and got shrunk in piecemeal. and recovered storage back this is still an ongoing process. – JayM Nov 17 '17 at 4:13

Paul Randal who led the storage engine team in SQL 2005 recommended moving the data to a different filegroup. Personally I like this approach because it caused the least amount of free space left at the end of the process. I believe this was in his old MCM training videos.

Also having our databases in the PRIMARY filegroup led to a potential disaster when the GAM/SGAM for the primary filegroup was corrupted due to all the writes on a bad IO subsystem. Since the DB boot page is on the primary file group and other reasons, I now always keep the PRIMARY filegroup empty to limit corruption potential to it. Also if you do piecemeal restore and you restore the Primary filegroup, your database is technically online reducing the errors you are spitting out while the rest of the DB is up.

  • Ali - Yes i thought of doing this but the size of databases are huge and there is no much space left to create any more filegroups and migrate. – JayM Nov 17 '17 at 4:13
  • oh dang, then you might have to do a regular shrink to get enough free space back, then migrate or defrag. – Ali Razeghi Nov 17 '17 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.