Show progress of shrink file command

If you are ever shrinking a large data file (bad practice but necessary sometimes) and need to get some idea of the progress you can run the below SQL to get some kind of percentage done indication.

It’s not exact, but can help you to get an idea that something is happening and things are moving along.

select	T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
		, R.cpu_time, R.total_elapsed_time, R.percent_complete
from	sys.dm_exec_requests R
		cross apply sys.dm_exec_sql_text(R.sql_handle) T

If you have any other methods that you use, please share them in the comments below.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.