Wednesday, January 14, 2009

How long until the db shrink process is done?

Before I answer that question, I have to give some history with my association with database shrinkage. As a Developer DBA, i often needed to shrink databases. At first i would simply use the GUI. But I often grew impatient at the apparent lack of progress. I would grow ever increasingly uneasy at not knowing what was going on, how long it'd take, etc. As I learned DB skills, I realized that shrinking is not all it cracked up to be, and I often didn't want to shrink the files down. Maybe the log, but leave the mdf alone, and let it stay a size, so it doesnt have to grow next time it needs too. However, in certain situations, it's important to shrink away.

So, I profiled the gui, grabbed the actions performed and made myself a proc. This proc would be used for many years. The proc would not only do the actions for me, if I asked, but it would report to me as well. This became the default execution path; I would run this proc to report the database filenames with the appropriate shrink commands. And then I could pick and choose which ones I wanted to do. Thus allowing myself the ability to do only parts. My unease decreased, and all was happy.

Except that, on occasion, I would still be uneasy, when even one of the simple steps seemed to take longer than expected. I would start to panic. I often imagine rogue processes growing wings, taking on a life of their own, and starting to remove data, corrupt pages, etc. Of course this is only my imagination as I sit and watch the spinning wheel mesmerize me.

So, fast forward to today. A developer comes to me and asks me if his shrink process should take as long as it is taking. Its been running for 30 minutes so far. I sigh, and reply, yeah. Sometimes, it takes days. And all the while, I believe nefarious actions are being taken without my knowledge by this seemingly rogue process. How can I tell how long it should take? Without actually doing it, and remembering? How long should it take? Can it take a long time?

Well, I let him know that it can take a long time, and I have no idea how to check or know. He leaves thinking less of me, I'm sure.

At this point, I turn to the Twitterverse, and posed the question there. A very simple question.

#sqlquiz you are shrinking a database. its taking a while. how do you check progress?

I received quite a few responses. Some folks seemed to think like I did, simply sighing and wishing. However, AaronBertrand responded with this.

@tjaybelt I forget if shrink is one of the tasks that reports percent_complete in sys.dm_exec_requests. Somehow I doubt it.

This spurred others on, and tweeps started trying it out. Success was found, and we had a way to determine the percent complete on the shrinkage. Yeah. Hazzah!

So, when I got back in touch the developer, we tried it, and alas, it reported 86%. Yeah. We knew that it was doing what it had originally set out to do, and was not off removing indexes or truncing tables. It was still doing its shrinkage, just taking a very long time. But hearts were lightened, smiles increased, and laughter soon ensued. We knew! Had numbers!! And with those numbers, could guess execution time to completion.

So, if you read this far, or skipped down, I'll summarize.
To determine the percent complete of an operation, such as shrinking a db, you can use sys.dm_exec_requests to pull this value out, and feel better about everything.



Granted said...

That's twice this week I've actually seen Twitter be useful. Now I'm hooked.

Aaron Bertrand said...

You can also look at the estimated_completion_time column. I think this is in milliseconds. Caution: this is marked as internal only according to BOL. But could still be useful info to pull.

Smiles said...

Thanks Guy! This is very useful for me..btw I added u in facebook.

Patrick said...

Fantastic! I've been sitting here waiting for a 35 GB production backup to shrink and I thought it would never finish, now I know when it will, or atleast its getting closer ;)

Nick Large said...

Based on the info given in this post, here is a query that produces the information discussed:

select session_id, command, round(percent_complete, 2) as percent_complete,
( (estimated_completion_time/1000) / 60 ) as estimated_completion_minutes_full,
( (estimated_completion_time/1000) / 60 )
/ 60 ), 1)) + ' hour ' +
(( (estimated_completion_time/1000) / 60 ) -
( (estimated_completion_time/1000) / 60 )
/ 60 ), 1) * 60))) + ' mins ' as estimated_completion_hours_mins,
DATEADD(MI, ( (estimated_completion_time/1000) / 60 ), CURRENT_TIMESTAMP) as estimated_completion_clocktime
from sys.dm_exec_requests
where session_id = 75

Thanks Aaron and TJ.

Dan said...

a quick addition to Nick Larges comment/query.

I added a where statement to display all current jobs:

Where estimated_completion_time > CURRENT_TIMESTAMP

Supplement Pro said...

Where estimated_completion_time > CURRENT_TIMESTAMP ?? You can't add that to Nick's query, you would get a Arithmetic overflow error converting expression to data type datetime.

Supplement Pro said...

Where estimated_completion_time > CURRENT_TIMESTAMP ?? You can't add that to Nick's query, you would get a Arithmetic overflow error converting expression to data type datetime.