As a DBA you may face a few common questions whenever someone accidentally deleted some database objects or the whole database.
“How long-time SQL Server will take to complete the database Backup or Restore? Yes DBA speaks out!! move your jaw and tell something!!”
Performing database refresh activity, database migration activity, or any adhoc activity where DBA is handling a huge size database need to answer higher authorities or managers about the estimated time amount to do the activity.
In this post, we will try a simple script to get the estimated amount of time that SQL Server is going to take to complete database Backup or Restore.
This script can be used to find the estimated time of backup and restore that is on progress in your SQL server. This script is applicable for SQL Server 2005 and above. While your backup and restoration activity is going on, execute the below script.
Script
1 2 3 4 5 6 7 8 9 |
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) AS [SQL] FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP LOG', 'BACKUP Database') |
Sample output
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…
Thanks, DEB, it’s a very essential script.
Keep posting such scripts.