For DBAs one of the most performed activity is taking Database Backups and Restoring Backups. I have always observed this trend that DBAs work under constant pressure and more often than not the maintenance time period that is alloted to DBAs are not sufficient to perform all the necessary activities. It has happened with me several times and I am sure it has happened with many DBAs as well that whenever any Database Backup Job or Database Restoration Job is taking more time than usual then everybody starts screaming even if they do not have any idea about how database works. It surely is not a pretty situation to be in.
Well, Microsoft is trying to make the life of DBAs little easier by introducing many new DBA friendly features and DMVs starting from SQL Server 2005.
One of the many wonderful DMVs that is there in SQL Server 2005 as well as SQL Server 2008 is “sys.dm_exec_requests”. With this DMV we can check the status and Percent_complete state for the below operations:
- DBCC CHECKDB
- DBCC SHRINKDATABASE
- DBCC SHRINKFILE
One of the query that I use very often to get all the information regarding the DATABASE RESTORE and DATABASE BACKUP is as follows:
dmv.PERCENT_COMPLETE AS [PercentComplete],
dmv.TOTAL_ELAPSED_TIME/60000 AS [Elapsed_Time_in_Minutes],
dmv.ESTIMATED_COMPLETION_TIME/60000 AS [Time_Remaining_in_Minutes],
(SELECT TEXT FROM sys.dm_exec_sql_text(dmv.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES sysdb, sys.dm_exec_requests dmv
WHERE sysdb.DBID=dmv.DATABASE_ID AND dmv.COMMAND LIKE ‘%BACKUP%’
ORDER BY percent_complete desc,dmv.TOTAL_ELAPSED_TIME/60000 desc
This query has helped me a lot to give the answers to all the anxious people and teams and to get all the necessary information for my purpose and planning as well. I hope this query will be handy for you as well. Let me know!!