Home / server / Questions / 31554
Accepted
dance2die
Asked: 2009-06-26 05:44:32 +0800 CST

How to check progress of DBCC SHRINKFILE?

  • 772

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
sql-server sql-server-2005 sql-server-2008 shrink

6 Answers

  1. Best Answer

    Have you checked percent_complete in sys.dm_exec_requests?

    • 43

  2. 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.

    • 19

  3. Query below will show you result like this: track dbcc shrink status

    -------------------------------
--Track DBCC shrink status
-------------------------------
select
a.session_id
, command
, b.text
, percent_complete
, done_in_minutes = a.estimated_completion_time / 1000 / 60
, min_in_progress = DATEDIFF(MI, a.start_time, DATEADD(ms, a.estimated_completion_time, GETDATE() ))
, a.start_time
, estimated_completion_time = DATEADD(ms, a.estimated_completion_time, GETDATE() )
from sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
where command like '%dbcc%'
    • 13

  4. Adding my own version for anyone interested, this converts the millsecond time columns into more readable minutes and seconds.

    select 
[status],
start_time,
convert(varchar,(total_elapsed_time/(1000))/60) + 'M ' + convert(varchar,(total_elapsed_time/(1000))%60) + 'S' AS [Elapsed],
convert(varchar,(estimated_completion_time/(1000))/60) + 'M ' + convert(varchar,(estimated_completion_time/(1000))%60) + 'S' as [ETA],
command,
[sql_handle],
database_id,
connection_id,
blocking_session_id,
percent_complete
from  sys.dm_exec_requests
where estimated_completion_time > 1
order by total_elapsed_time desc
    • 7 
  5. SELECT 
    d.name,
    percent_complete, 
    session_id,
    start_time, 
    status, 
    command, 
    estimated_completion_time, 
    cpu_time, 
    total_elapsed_time
FROM 
    sys.dm_exec_requests E left join
    sys.databases D on e.database_id = d.database_id
WHERE
    command in ('DbccFilesCompact','DbccSpaceReclaim')
    • 5

  6. Or you can just run exec sp_who3.

    • -1