Sql Server Check the status or progress of backup job
- Posted by Sqltimes
- On October 30, 2013
- 0 Comments
Quick one today:
Sometimes when I run manual backup processes, I want to check how far along has it progressed and its current status. There is an easy way to do this. As you know, the DMV ‘sys.dm_exec_requests’ has information about current requests to the database. If we query that and join it with ‘sys.dm_exec_sql_text’ we can capture all the necessary information.
This is the query:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SELECT DB_NAME(R.database_id) AS [Database_Name] , R.Command AS [Command] , R.status AS [Status] , R.start_time AS [Start_Time] , R.percent_complete AS [Percent_Complete] , R.total_elapsed_time/(1000*60) AS [Duration_in_Minutes] , R.estimated_completion_time/(1000*60) AS [Estimated_Completion_Time_in_Minutes] , T.text AS [Sql_Text] , R.wait_type AS [Wait_Type]FROM sys.dm_exec_requests AS RCROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS TWHERE R.command in ( 'RESTORE DATABASE' , 'BACKUP DATABASE' , 'RESTORE LOG' , 'BACKUP LOG' ) |
Above code spits out all the necessary information along with some more pertinent info.
Backup_Progress_and_Status
Hope this helps,
_Sqltimes
0 Comments