Quick one today:

Very often, we get this question about how to check the progress of certain long running activities. There are several ways to check different processes, but there is no single answer. The best answer is as always ‘it depends’ – because different processes takes different techniques.

Today, we’ll look at one such technique.

Starting Sql Server 2005, Microsoft has added a DMV that provides status or progress of certain tasks. With later versions a.k.a. Sql Server 2008, 2008 R2, 2012, etc, the scope has been expanded to cover more tasks. One exception is that this does not provide progress for data retrieval queries.

The DMV ‘sys.dm_exec_requests’ provides several important and useful internal data along with ‘percent complete’ and ‘estimated completion time’. Both are helpful in planning and preparation.

Percent Complete

Based on MSDN, we could query status or progress for the following tasks:

• ALTER INDEX REORGANIZE
• AUTO_SHRINK option with ALTER DATABASE
• BACKUP DATABASE
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKTABLE
• DBCC INDEXDEFRAG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• RECOVERY
• RESTORE DATABASE,
• ROLLBACK
• TDE ENCRYPTION

In the past, we had a post that show how to check the progress of backup job using this DMV.