SQL Server Tips: How to know how long goes a shrink ?

Few days ago, I perform a shrink on a data file to spend a little bit space on a disk.
The SQL Server is a test server and the customer does not want to expand the disk.

I run the shrink like usual and waiting….

After ~15 minutes, I don’t know how much time I need to wait until it’s finished…

I decide to create a query to know it:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as
estimated_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command like 'DBCC%'

This query is based on sys.dm_exec_requests and sys.dm_exec_sql_text.
The useful columns are the 2 last one with the percent_complete and the estimated_completion_time.

In my example, the query running already 15 minutes for 75% done and finish in 5-6 minutes…

Well, it’s a good news, the shrink finish soon…But at the end, he finish 33 minutes later and not at 20 minutes.

Why, why, why?

The reason is easy, the query is not every time in a running state and can also be suspended or runnable.

The query will give you a good point to know the status of your shrink with the column percent_complete but don’t use the estimated_completion_time to believe that the shrink will finish in 5 minutes! 😉

Happy to share your experience with it!