Ah, SQL database maintenance… always so much fun! Well, for DBAs, anyway.
I can’t speak for DBAs, but I know that one of my pet peeves as a System Administrator has been the lack of progress bars on SQL. Especially with commands such as Shrink Database. Sometimes this command takes some time, and as time goes on, you start wondering… is it actually processing in the background? Or is SQL Management Studio, or worse yet, the server locked up? How much longer is it going to take?
Fortunately, although there isn’t a nice easy to read progress bar, there is a way to determine how far along your Shrink Database task is.
First, open up SQL Management Studio, and open a new query on your selected database.
Then, input this command:
SELECT percent_complete FROM sys.dm_exec_requests WHERE command = 'DbccFilesCompact'
The query will output the percentage in a decimal form, easy as that!
Want a little more detail? Try this:
command = 'DbccFilesCompact'
And you’ll get more detailed results, as per the following: