Microsoft – SQLServer – Percent Complete

Microsoft – SQLServer – Percent Complete

The % complete indicator is a very helpful tool to determine whether the command you have instructed SQL Server is being serviced, whether it is stuck, and generally how far off one is…

Unfortunately, the indicator is not available for all commands.

Here are a few cases whence it is available:

  1. Database Recovery (when SQL Server is restarted, existing databases are brought back to live.  In some cases, it might take a while for this operation to complete)
  2. Database Backup
  3. Database Restore
To determine percent complete issue a query against the sys.dm_exec_requests DMV (Dynamic Management View).
             , status
             , command
             , database_id as databaseID
             , db_name(database_id) as databaseName
             , blocking_session_id
             , wait_type
             , percent_complete
   from   sys.dm_exec_requests tblRequests

The key column\indicator is percent_complete.

Off course, you might want to correlate against session_id, status, command, database_id, and wait_type.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s