I have a 5 TB database file (don't ask why) that I am cleaning up. I was already able to free over a TB and I will probably end up at 2.5 TB.

All best-practice advice aside, I need to now shrink this file. I am currently running this statement:


I can see that it is doing something, as it is sometimes waiting for a pageiolatch_sh and sometimes for a pageiolatch_ex. However, after 24 hours of execution time, the database has not shrunk by a single page. Usually when executing a file shrink like the above, you can see an impact on the file size immediately. Before, I tried to shrink the database by just a single GB and that also did not cause the file size to change at all. I stopped it after 24 hours.

This problem has been lingering for a while but we are at a point were we need to make progress, as the drive space is urgently required for other databases.

BTW, DBCC CHECKDB does not find any problems with that database, so this behaviour can't be caused by some circular page linkage.

  • Did you try running DBCC SHRINKFILE (1,TRUNCATEONLY)? It can be a good start since it doesn't try to move any data inside the file – Matan Yungman May 18 '14 at 14:44
  • Do you have BLOB or TEXTIMAGE data? – swasheck May 18 '14 at 15:08
  • 1
    have you checked log_reuse_wait_desc column in the sys.databases system table? – James Anderson May 18 '14 at 18:39
  • @MatanYungman, I did, to no avail. The last extent of the file is in use... – Sebastian Meine May 18 '14 at 19:30
  • @swasheck, yes. In fact most of the files is BLOB data (including the last extent) – Sebastian Meine May 18 '14 at 19:31

A colleague of mine had a similar-ish problem with long-running shrinks. He took some time to investigate it and found that he had lots of indexes at the 'end' of the file.

A little index maintenance, and then the shrink operation ran fine. You can read his blog about it here: http://sqltrees.wordpress.com/2013/08/11/shrinking-databases/

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.