Keeping track of DBCC SHRINKFILE, EMPTYFILE

The EMPTYFILE option is meant to give you a means to remove extra datafiles you added, particularly ones that have data in them. Executing this command will cause SQL server to rebalance data to other existing files within the filegroup. Its unusual to need to do this with tiny databases - you likely have to do this with existing, legacy systems that exploded in growth, existed through technological innovations, and likely are more complicated now than they need to be.

When executing this command, there are a litany of things you must consider - IO contention, user access, latching/locking/blocking, and given that you're likely executing this process  on big databases - progress. I use Adam Machanic's "SP_WHOISACTIVE" for so many things and this is no exception. One of the columns in his script output is "Percent Complete". In my cases, this column is unpopulated, likely due to the unpredictable nature of many SQL operations. Fortunately, percent complete is valid for the shrinkfile process! Use this to give you a better handle on just when your shrinkfile will complete!

Bringing it back

I originally created this blog as an opportunity to voice my experiences, attempts at researching the undocumented, and perhaps giving the interwebs a chance to learn from things I spent way too much time on. Like most good intentions, these efforts went stale rather quickly and I left my name associated with not much more than stub. Today, I'm going to reignite that energy.

These days I work as a Sr. DBA for a very large healthcare company. I don't spend as much time fiddling with hardware and quite alot more time dealing with application nuances and how hardware can be used to solve those challenges.