How to check progress of DBCC SHRINKFILE?

Question

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

score 48 · Accepted Answer · edited Jun 16 '25 at 18:37

48

Have you checked percent_complete in sys.dm_exec_requests?

select percent_complete from sys.dm_exec_requests where command like '%dbcc%'

edited Jun 16 '25 at 18:37

malakrsnaslava

103

answered Jun 25 '09 at 13:48

Aaron Alton

1,168

score 19 · Answer 2 · edited Aug 10 '24 at 13:01

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 that shows 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.

score 18 · Answer 3 · answered Aug 18 '17 at 20:38

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%'

score 7 · Answer 4 · answered Nov 15 '16 at 17:57

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

score 5 · Answer 5 · answered Jun 16 '15 at 10:06

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')

score 0 · Answer 6 · answered Aug 06 '24 at 09:32

As Paul Randal says, you should not shrink your files.

However, I got into situations that I need to shrink files, for situations like: in the live database docs and images are in the files, however, we dont need most of these in dev and uat environments, so yeah, I shrink the files because a database of 200GB is much easier to handle than a database over a terabite.

and to check the progress of the restore, progress of the backup, and progress of the shrink operation I have this script:

                SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
                SET NOCOUNT ON
                --check the progress of backup, restore or shrinking
                --marcello miorelli v.20240901
            SELECT 
              [Server Name]                = @@servername
             ,s.host_name
             ,TheUser                      = s.original_login_name
             ,r.session_id
             , r.command
             ,[Percent Complete]           = CONVERT(NUMERIC(6,2), r.percent_complete)
             ,[Start time]                 = CONVERT(VARCHAR(30), r.start_time ,113)
             ,[ETA Completion Time]        = CONVERT(VARCHAR(30), DATEADD(ms,r.estimated_completion_time,GetDate()),113)
             ,sdest.Query
                     FROM sys.dm_exec_requests r WITH(NOLOCK)
               INNER JOIN sys.dm_exec_sessions s WITH(NOLOCK)
                       ON r.session_id = s.session_id
              OUTER APPLY (

                            SELECT COALESCE((
                                        SELECT TEXT AS [processing-instruction(_)]
                                        FROM sys.dm_exec_sql_text(r.sql_handle) 
                                        FOR XML PATH('')
                                            ,TYPE
                                        ), '') AS Query

                            FROM sys.dm_exec_sql_text(r.sql_handle) 

                ) sdest
                WHERE 1=1
                  AND r.command IN ('RESTORE DATABASE', 'BACKUP DATABASE','DbccFilesCompact','DbccSpaceReclaim')

this even gives you the script that has been used in the above operations. it is a helpful tool in any DBAs toolbox.

score -1 · Answer 7 · answered Mar 07 '18 at 09:40

-1

Or you can just run exec sp_who3.

answered Mar 07 '18 at 09:40

darotweiler

1

How to check progress of DBCC SHRINKFILE?

7 Answers7