47

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
Paul Randal
  • 7,184
  • 1
  • 35
  • 45
dance2die
  • 1,961
  • 7
  • 31
  • 40

6 Answers6

43

Have you checked percent_complete in sys.dm_exec_requests?

Aaron Alton
  • 1,118
  • 11
  • 10
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.

Paul Randal
  • 7,184
  • 1
  • 35
  • 45
  • 5
    "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! – dance2die 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 00: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 '17 at 12:49
  • 1
    Link to blog post is broken, this appears to be valid now: https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ – Jonathan Gilbert Jun 26 '19 at 16:52
13

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%'
Mark Varnas
  • 231
  • 2
  • 5
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
user5947282
  • 71
  • 1
  • 2
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')
Francisco Figueiredo
  • 61
  • 1
  • 1
  • 2
    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. –  Jun 22 '15 at 14:53
-1

Or you can just run exec sp_who3.

darotweiler
  • 1