Script to Monitor the progress of ALTER, Backup, Restore, DBCC, Rollback and TDE commands

1 Reply

We often like to check the progess or completed percentage of time consuming command. You can query the sys.dm_exec_requests DMV to check the status of the command.

You must have atleast view state permission to execute the below query.
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

SELECT dmr.session_id,
       dmr.status,
       dmr.start_time,
       dmr.command,
       dmt.TEXT,
       dmr.percent_complete
FROM sys.dm_exec_requests dmr  CROSS APPLY sys.Dm_exec_sql_text(dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’,  ‘TDE’)
Advertisements

1 thought on “Script to Monitor the progress of ALTER, Backup, Restore, DBCC, Rollback and TDE commands”

  1. sql

    corrected sql syntax.

    SELECT *
    FROM
    sys.dm_exec_requests
    dmr
    CROSS APPLY
    sys.dm_exec_sql_text (dmr.sql_handle) dmt WHERE dmr.command IN (‘ALTER’, ‘Backup’, ‘Restore’, ‘DBCC’, ‘Rollback’, ‘TDE’)

    Reply ↓

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Cancel

Connecting to %s