Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

2 Answers 2

up vote 15 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
1  
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
2  
"I used to own the shrink code, so I know what I'm talking about." nice! –  splattne Jun 25 '09 at 15:32
1  
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
1  
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

Your Answer

 
discard

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.