TSQL Query to find the DB Maintenance status

TSQL Query to find the DB Maintenance status

Are you curious to find the Database Backup / Restore status?
Do you have to find out the status the DBCC CHECKDB ?
Do you have to report when the ROLLBACK would finish ?
Do you want judge how long the Database Shrink will take?

Here is the answer!

The query listed below can be used to find the Percentage of work completed for the following commands:

1. ALTER INDEX REORGANIZE
2. AUTO_SHRINK option with ALTER DATABASE
3. BACKUP DATABASE
4. DBCC CHECKDB
5. DBCC CHECKFILEGROUP
6. DBCC CHECKTABLE
7. DBCC INDEXDEFRAG
8. DBCC SHRINKDATABASE
9. DBCC SHRINKFILE
10. RECOVERY
11. RESTORE DATABASE,
12. ROLLBACK
13. TDE ENCRYPTION

We have to replace the where condition filter based on the requirement.

SELECT command,
            sqltext.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests Req
CROSS APPLY sys.dm_exec_sql_text(Req.sql_handle) sqltext
WHERE req.command in ('DBCC CHECKDB')