The day begins and you find yourself going through a list in your mind of things that need to be accomplished. You either make a mental note, jot it down on paper, or input notes into your mobile device. The day is getting off to a great start; you feel as though you have a sense of direction and purpose before you even open up shop for the day. It is then the phone rings or you get alerted to trouble; issues on the horizon.
The issue at hand requires a backup or restore to be completed and you find yourself dependent on the mercy of SQL processing the request. We’ve all been there; phone rings again and it is someone on the other line asking you:
- Are we done yet?
- What’s the status?
- How much longer will this take?
These are all legitimate questions that you will be asked, and it’s okay. Pressure situations are opportunities to make it happen. Over the years data professionals have built out their own script and document libraries. I’ve carried scripts around for years, one script I like to utilize regarding backups and restores is below. I have some variations to this script with what it pulls back, but the standard script hits the sys.dm_exec_requests DMV.
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)
))
FROM sys.dm_exec_requests r
WHERE command IN ( ‘RESTORE DATABASE’, ‘BACKUP DATABASE’ );
By checking only for the restore and backup command lines you will be able to quickly identify your session id and get an approximate ETA and percentage complete. you can tinker of course with the estimations if you’d like or pull back more fields. This is just a simple technique in utilizing a helpful DMV to provide info quickly.
Additional information
Aaron Bertrand (b|t) wrote a post several years ago around sys.dm_exec_requests that has good information in it that you can find here.
You can find the MSDN listing for sys.dm_exec_requests here.
What are DMV’s?
DMV’s are Dynamic Management Views within SQL that can help with a myriad of troubleshooting, performance tuning, and overall health of a system. You can find all the categories for DMV’s and learn more about them here.
Wrapping Up
I’ve been on both sides of the fence in the past where third-party tools are not always possible to have. These DMV’s can be life savers in certain situations; you can parachute in and parachute out gathering knowledge on any given situation. I urge you to explore and learn new things that may help you in your future.
**DISCLAIMER – Do not take code blindly from the internet because you found it on a blog and execute it without first testing it yourself.**
