Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
- sqlserver-dba.com switching from feedburner to follow.it
- Taking transaction log backup and full back at the same time
- How do I find hidden files taking up space?
How to Predict SQL BACKUP DATABASE finish time with sys.dm_exec_requests
23 August,2012 by Tom Collins
Predicting the finish tme of a SQL BACKUP DATABASE command is useful. The sys.dm_exec_requests DMV offers some good clues to the finish time.
I was shrinking a log file (due to long running transaction with no built in COMMITS ) that required some management but the command wasn’t executing successfully , due to a running BACKUP DATABASE command . I needed to know the percentage finished of the BACKUP DATABASE command
Running this script ,utilising sys.dm_exec_requests I can see the estimated finish time . I’ve included a sample query , which returns a percent complete and estimated completion.
SELECT session_id,percent_complete,DATEADD(MILLISECOND,estimated_completion_time,CURRENT_TIMESTAMP) Estimated_finish_time, (total_elapsed_time/1000)/60 Total_Elapsed_Time_MINS , DB_NAME(Database_id) Database_Name ,command,sql_handle FROM sys.dm_exec_requests WHERE command LIKE ‘%BACKUP DATABASE%’
Read More
SQL Server faster restores with instant file initialisation
Display sql backup history for a single database
SQL Server - Display restore history for a single database
!
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |