Thursday, November 18, 2010

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')