获取备份恢复和索引重组进度的脚本

Aaron Bertrand

When will my backup / restore / index reorganize finish?

from: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/30/when-will-my-backup-restore-index-reorganize-finish.aspx 

 

The DMVs are a great way to get a closer look at what is actually going on in your system.  In particular, for some operations, SQL Server populates the percent_complete and estimated_completion_time (in milliseconds) in the DMV sys.dm_exec_requests.  This can be very useful if you want to know how long your BACKUP or RESTORE will take (if you didn't use WITH STATS or are using command-line or a 3rd party tool), or whether you should go to lunch while your ROLLBACK is, well, rolling back.

I've blogged about this in the past, but I frequently find it useful to come back and use this same type of query for things I'm working on today.  For example, I am setting up log shipping for a largish database this afternoon, and I wanted to monitor its progress.  Then I thought to expand my query to include any other query whose progress I *could* monitor.  And then I thought to include a couple of other interesting data points, such as host name / I address, the actual command, and which procedure the command came from (if applicable).  And then I thought, hey, maybe that would be useful to other people as well.  So here is the script I have refreshed several times impatiently while writing this post:

SELECT    r.[session_id],    c.[client_net_address],    s.[host_name],    c.[connect_time],    [request_start_time] = s.[last_request_start_time],    [current_time] = CURRENT_TIMESTAMP,    r.[percent_complete],    [estimated_finish_time] = DATEADD        (            MILLISECOND,            r.[estimated_completion_time],             CURRENT_TIMESTAMP        ),    current_command = SUBSTRING        (            t.[text],            r.[statement_start_offset]/2,            COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647)        ),    module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid]))         + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')  FROM    sys.dm_exec_requests AS r  INNER JOIN    sys.dm_exec_connections AS c    ON r.[session_id] = c.[session_id]  INNER JOIN    sys.dm_exec_sessions AS s    ON r.[session_id] = s.[session_id]  CROSS APPLY    sys.dm_exec_sql_text(r.[sql_handle]) AS t  WHERE    r.[percent_complete] <> 0;

As a recap, the types of operations that are exposed in the percent_complete and estimated_completion_time are:

• BACKUP
• RESTORE
• ALTER INDEX ... REORGANIZE (not REBUILD)
• ROLLBACK
• Many DBCC commands

These are not documented at all, so I thought I would take this opportunity to point you to a still-active Connect item I filed over two years ago:

#284207 : DOC : provide more info on percent_complete (sys.dm_exec_requests)

I also think that the Books Online topic "sys.dm_exec_requests," which had examples in SQL Server 2005 that were ripped out in SQL Server 2008, could be enhanced with some more useful examples:

#354545 : DOC : sys.dm_exec_requests topic enhancements

Fellow MVP Grant Fritchey just wrote about an interesting and should-be-obvious-but-isn't approach to tracking statement-level execution within a batch - periodically storing the last_request_start_time so you could go back and map out exactly how long each request in a batch took.  I'd be lying if I said his post didn't inspire this one in some ways... mostly to separate the batch-level start time from the statement-level start time (this makes the percent_complete calculation make a lot more sense).  I don't have the time or motivation today to expand on his post, but I hope to do so sometime in the near future.

As an aside, do you find this kind of post useful, boring, something else?  I often feel motivated to share the kinds of things I am doing from day to day, but I'd love to hear feedback about what kinds of things *you* want to read about.