Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to find out the progress of DBCC SHRINKFILE statement?

Here is how I was running it

dbcc shrinkfile('main_data', 250000)

I am running above statement on both SQL Server 2005 and 2008.

[UPDATE] Here is the query I ran to check the progress and the text that's being run.

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
share|improve this question
up vote 21 down vote accepted

Have you checked percent_complete in sys.dm_exec_requests?

share|improve this answer
Currently checking out how to read data returned from that DMV. – Sung Jun 25 '09 at 14:51
This is it, Aaron! Thanks – Sung Jun 25 '09 at 15:36

Aaron's answer is spot on, but I'd like to caution you against running data file shrink as it causes horrible performance problems. I used to own the shrink code, so I know what I'm talking about. Checkout this blog post I wrote yesterday that shows you what I mean and advises how to effect a shrink without actually doing a shrink: Why you should not shrink your data files

Hope this helps!

PS One more thing to check if it's taking a long time and the percent_complete is not increasing - look for blocking. Shrink will infinite-wait for locks it needs.

share|improve this answer
"I used to own the shrink code, so I know what I'm talking about." nice! – splattne Jun 25 '09 at 15:32
It was taking forever to shrink 600G data file... I will read it over and consider using index defrag. Thanks Paul! – Sung Jun 25 '09 at 15:44
keep in mind I found this answer cause I was looking for progress on DBCC SHRINKFILE (MyFile, EMPTYFILE) ... I am moving data between drives by adding a file to the filegroup on the new drive, emptying the original and dropping it. – Sam Saffron Feb 23 '12 at 0:19
    sys.dm_exec_requests E left join
    sys.databases D on e.database_id = d.database_id
    command in ('DbccFilesCompact','DbccSpaceReclaim')
share|improve this answer
It may be helpful to include a description of what your code does in your answer – BE77Y Jun 16 '15 at 10:11
+1 for an excellent query, but -1 for repeating what the OP said six years after the fact. If your query is better than his in some respect, please describe it, otherwise it's just wasted space. – user82769 Jun 22 '15 at 14:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.