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!