53

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
Improve this question

6 Answers 6

Reset to default
47

Have you checked percent_complete in sys.dm_exec_requests?

Improve this answer
1
  • Currently checking out how to read data returned from that DMV.
    – dance2die
    Jun 25, 2009 at 14:51
19

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.

Improve this answer
5
  • 6
    "I used to own the shrink code, so I know what I'm talking about." nice!
    – splattne
    Jun 25, 2009 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!
    – dance2die
    Jun 25, 2009 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, 2012 at 0:19
  • @Paul, I am observing that shrinking a file to a target size takes a while but completes (I see it looking at the file size)-but although it SEEMS to be completed succesful, the process of shinking still continues and runs forever. Same with smaller (some MBs or larger (1 GB) amounts of shink volume). sys.dm_exec_requests continuously shows endless activity, changing resource locks while at same time percent_completion stucks at something around 32,8%. At this point I CANCEL the process and offically celebrate a success - knowing that SOMETHING still was going... any idea what's wrong? 2008r2
    – Magier
    Jan 12, 2017 at 12:49
  • 1
    Link to blog post is broken, this appears to be valid now: sqlskills.com/blogs/paul/…
    – Jonathan Gilbert
    Jun 26, 2019 at 16:52
15

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%'
Improve this answer
7

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
Improve this answer
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')
Improve this answer
2
  • 2
    It may be helpful to include a description of what your code does in your answer
    – BE77Y
    Jun 16, 2015 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, 2015 at 14:53
-1

Or you can just run exec sp_who3.

Improve this answer
1
  • 4
    This would need a better explanation.
    – Sven
    Mar 7, 2018 at 10:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .