Check the Status of a SQL Database Shrink Operation

I’m a sysadmin, not a DBA, yet I still find myself frequently doing work in Microsoft SQL Server. While preparing for an application upgrade, I saw a database grow from 40 GB to 52 GB in less than a month. Backups were already taking long enough—and we were required to run a backup between every step the upgrade—so I decided to try my luck with shrinking the DB. Ninety minutes later, it was still not done and there was no status bar, so I hit Bing. “How long does a database shrink take?”

Here is a great command that I found to show the current status of a SQL database shrink operation:

select percent_complete from sys.dm_exec_requests where command = 'DbccFilesCompact'

Hope you find that one as useful as I did!

Aside: yes, I used Bing. Figured it was time to try something other than Google and see how the results compared. It feels weird to be unable to use “google” as a verb!

Update: I would not recommend using a shrink operation in most situations, but I had tried this when I knew less about its use in SQL. The main learn from this was how to use “select percent_complete.”


Tags: sql sysadmin dba

Problem with Least Privilege Approach to Using AGPM

If you have software assurance licensing in your Windows environment, then I’m sure you are taking advantage of the Advanced Group Policy Management (AGPM) tool that is included in the Microsoft Desktop Optimization Pack (MDOP).  Of course, since you are a wise sysadmin, I’m sure that you are also employing least privilege concepts in your work.  That is, your primary logon as a systems administrator is not with a domain admin privileged account.  Instead, you log on as a non-privileged user and use “run as” to launch tools such as the Group Policy Management Console and Active Directory Users and Computers.  Right?  Good.

I am a big fan of both, but recently ran into a problem with my AGPM client after migrating the AGPM archive to a different server.  I couldn’t connect any more!

The actual AGPM migration was straightforward. In short, it required installing the AGPM server on a new machine, copying the archive folder to the new machine, and updating the AGPM GPO to point client connections to the new server instead of the old one.  After all of this, I ran gpupdate /force on my workstation so my Advanced Group Policy Management client would find the new server, then restarted for good effect.  Done, right?

The problem began when I right-clicked on the shortcut, selected “Run as different user” and entered my domain admin credentials.  It couldn’t connect—AGPM was still pointing to the old server.  (I couldn’t change it because the setting was controlled by a policy.)  Hmmm, maybe the GPO hadn’t replicated and been applied to my machine yet.  Force AD replication, gpupdate, reboot, try again.  Nothing!  Frustration.  Repeat. Repeat. Repeat. 

Let’s make a long story short, shall we?  I finally realized that when you control the AGPM server connection for clients via GPO, it is a user setting.  My standard, non-privileged account had up-to-date GPOs, but by privileged account had never been refreshed. 

The easy solution was to log in to the workstation using my privileged domain admin account, allow the updated AGPM group policy to get applied, and then log back in with my non-privileged account.  Right-click the Group Policy Management Console, Run As, and enter my privileged user credentials. Boom! We’re back in business, and still safely using least privilege every day.  Am I the first one that missed this obvious problem when using least privilege with AGPM?