XML FeedsSometimes you want to quickly see if there are any databases or logs being backed up or restored at this moment. I blogged at one point how you can check how much longer the restore will take here: How much longer will the SQL Server database restore take. The other day someone wanted to know this information for all databases on a server, he wanted to know this for restores as well as backups. The query below will give you that info as well as the percentage that is complete for each operation
tsql
- SELECT
- d.PERCENT_COMPLETE AS [%Complete],
- d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
- d.ESTIMATED_COMPLETION_TIME/60000 AS TimeRemainingMin,
- d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
- d.ESTIMATED_COMPLETION_TIME*0.00000024 AS TimeRemainingHours,
- d.COMMAND as Command,
- s.text as CommandExecuted
- FROM sys.dm_exec_requests d
- CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)as s
- WHERE d.COMMAND LIKE 'RESTORE DATABASE%'
- or d.COMMAND LIKE 'RESTORE LOG%'
- OR d.COMMAND LIKE 'BACKUP DATABASE%'
- OR d.COMMAND LIKE 'BACKUP LOG%'
- ORDER BY 2 desc, 3 DESC
Throw this in a view on your Tools database and you are all set.
This will probably also be added to SQLCop's informational section
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.