3

Running SQL 2012 R2...

Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) Standard Edition (64-bit)

Ran the shrink command

DBCC SHRINKDATABASE(databaseNameHere)

(Yes, I know this shouldn't be done regularly, if ever... we have a 200 GB database and cleared out many years worth of data, and should be able to reclaim about 100 GB of space)

When I checked the status of the task at 1.5 hours, it was 49.1xxx percent_complete.

It's been running for 2.5 hours... and now at 49.5xxx percent_complete.

Additionally, just in the last 20 minutes, the estimated_completion_time (found in sys.dm_exec_requests) has gone from 8,741,035 milliseconds to 9,385,086 milliseconds...

There is still a ton of space available on the drive. It is a development/test database that nobody is using... so whats the deal? why does the estimated time keep increasing?

I've been using sp_who2 active to verify there are no blocks...

  • 1
    Please add you SQL Server version. Some comments in this forum might be helpful for you.[Anyone Know Why DBCC ShrinkFile Takes So Long? ](social.msdn.microsoft.com/Forums/sqlserver/en-US/…) – SqlWorldWide Apr 4 '18 at 14:47
  • Why on earth you are shrinking whole database, I am sure you are aware that it is bad but shrinking whole database is blunder. You may stop it and shrink either data file or log file from where you want to extract space. Shrinkdatabase is even more evil than shrinkfile. Please check if the process is blocked ? – Shanky Apr 4 '18 at 14:48
  • @Shanky I've been using "sp_who2 active" to verify there are no blocks. After reading an article from the guy who owned the shrink code at Microsoft (sqlskills.com/blogs/paul/…) , it seems running a shrink mostly just fragments your indexes, which can be fixed by running a INDEXDEFRAG. – adam Apr 4 '18 at 15:08
  • @SqlWorldWide thank you for the tip. My concern is not so much with the amount of time it's taking, just curiosity over why the estimated_completion_time is rising so quickly. – adam Apr 4 '18 at 15:11
4

SHRINKDATABASE and SHRINKFILE won't actually release the space to disk until the very last moment: it has to move all the contents around within the files first (which is the part that takes a long time).

For why the progress doesn't seem constant: the free/used space is spread out across a large file, so it is going to "skip ahead" when it encounters an empty patch and "slow down" when it hits a section of used pages.

As mentioned in the comments, I would highly recommend using SHRINKFILE instead of SHRINKDATABASE, since you can control the target sizes of each individual file, and give each one a reasonable target. For example, I usually try to leave 15-25% free space in each data file.

  • Thank you for the clarification! Since our database only has one Filegroup (created prior to me) , the main thing that seems to be missing would be controlling the free-space left. SHRINKDATABASE has an option for TargetPercent, so isnt that just as good? – adam Apr 4 '18 at 15:17
  • 1
    @adam No, because you're still not distinguishing between size/free space of the data file vs the log file. Log files are mostly empty space anyway (unless a large transaction is currently in progress), so "target percent free" isn't a useful way to think about it. I target my log file size as a percentage of the data file size, with an additional adjustment depending on FULL vs SIMPLE recovery mode. – BradC Apr 4 '18 at 15:27

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.