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.

Yeah!!