Sql-server – SQL Server shrink command ‘estimated_completion_time’ keeps going up

shrinksql serversql server 2014

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…

Best Answer

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.

Related Solutions

Sql-server – Shrink SQL Server DB By xgb

Yes, you can, but it is not a good idea unless you are 100% certain the database won't grow again. If it will, then shrinking the file to free up space is like vacuuming while confetti is still falling on your carpet, and you should really read one of the many posts out there on how futile it is to shrink files that are just going to grow again. Nothing is gained except your free space % looks a little better for a little while. Big whoop! Do you get a bonus for how much free space is on your disk, even if you can't sustain it?

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Why Does the Transaction Log Keep Growing or Run Out of Space?

Now, if you really, really, really want to shrink your files, even after reading all of the above material and refusing to heed our warnings, keep reading.

If you want to shrink the data file by 10GB, look at the size it is now, and then use DBCC SHRINKFILE (not DBCC SHRINKDATABASE) and set a size 10GB smaller than that. Look at the size column from sp_helpfile - let's say the file is now 41 GB, and you want it to shrink to 31 GB, then you would say:

DBCC SHRINKFILE(filename, 31000); -- 31,000 MB

You can probably do better math than that (I know 10,000 MB is not exactly 10 GB but you get the idea).

Sql-server – Should I shrink this SQL Server data file

Presuming you are sure the database is not going to need the 200+ GB of extra space it is currently consuming, I would recommend performing a shrink without reorganizing pages, using TRUNCATEONLY:

DBCC SHRINKFILE (N'<file name>' , 0, TRUNCATEONLY);

TRUNCATEONLY simply truncates the last part of the file without moving rows inside the data file. This is important since it will prevent logical fragmentation of tables inside the data file. If you perform the shrink without specifying TRUNCATEONLY SQL Server will move pages around inside the file to get the smallest possible file size, thereby creating anywhere from "some" to "a lot" of fragmentation, which may adversely affect performance. Read more about this at the Books Online page for DBCC SHRINKFILE. Thanks to @Zane for the suggestion to include these details in my answer.

Once you have performed the shrink, you may want to ensure the file growth parameters are set correctly. For instance, ensure the file growth is not percentage based; use a number of MBs that makes sense for your expected growth rate.

Best Answer

Related Solutions

Sql-server – Shrink SQL Server DB By xgb

Sql-server – Should I shrink this SQL Server data file

Related Question