Get backup and restore performance stats with a DMV

Published 12 June 07 06:25 AM | SQL Master 

Normally the backup and restore operation will be quick enough or depends upon the server configuration to complete. Say if you have started with a 2GB size of database and over the time as that database is updated, the amount of data that is included in differential backups increases.

This makes the backup slower to create and to restore. Eventually, another full backup will have to be created to provide a new differential base for another series of differential backups.  This is applicable even though you are using only FULL database backups scenario as the each backup also contain sufficient log to roll back uncommitted transactions to bring the database into a state that is transactionally consistent and usable. Each restore scenario is implemented by using one or more restore steps (operations), called a restore sequence. Each operation corresponds to an individual TSQL RESTORE statement. A restore sequence moves affected data through one or more of the phases of restore. Ok enough said on the process of backup and restore, now coming to the point about knowing the values for percent_complete and estimated completion time for a database over 100 GB in size.

Using another DMV in SQL Server 2005 by the way of following query gives that information: 


SELECT database_id,cpu_time,status,command,percent_complete ,((estimated_Completion_time/1000.0)/60)/60 as Estimated_timeinHRS from sys.dm_exec_requests where session_id= <SessionID>

The above DMV will behave differently prior to Service Pack 2 on SQL Server 2005 as they were few changes affected to the system tables and DMVs that are associated with sys.dm_exec_requests view.

Overall do not forget to take help of PERFMON (SYSMON) by capturing Physical disk counters: read bytes/sec and write bytes/sec for the real time statistics.

Comments

# Get backup and restore performance stats with a DMV - SSQA - SqlServer-QA.net said on June 12, 2007 11:23 AM:

PingBack from http://www.msmvps.com/blogs/ssqa/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx

# Log Buffer #49: a Carnival of the Vanities for DBAs « Coskans Approach to Oracle said on June 15, 2007 5:50 AM:

PingBack from http://coskan.wordpress.com/2007/06/15/log-buffer-49-a-carnival-of-the-vanities-for-dbas/

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.