Overview

Many a time, we are asked by customers about the effect of overlapping backup schedules: for example, whether they can backup the transaction log at the same time that a long-running complete backup of the database is taking place.  Books Online does talk about the concurrency, but does not cover all versions or combinations.

Here is a short test case to illustrate the concepts involved. At the end of the post, I have provided a summary of what works in what version.

Test Case

I ran the 3 types of backups on a SQL 2005 / 2008 database at the same time:

  • COMPLETE a.k.a. FULL (backup database mydb to disk = 'c:\temp\mydb.bak' with init)
  • DIFFERENTIAL (backup database mydb to disk = 'c:\temp\mydb_diff.bak' with differential, init)
  • LOG (backup log mydb to disk = 'c:\temp\mydb.trn' with init)

Here is the output of each command for verbose reference. Also included immediately below is a snapshot from sysprocesses showing the blocking of the DIFFERENTIAL backup and the concurrent execution of the LOG backup.

spid blocked waittype lastwaittype waitresource cmd
61 0 0x0062 ASYNC_IO_COMPLETION                BACKUP DATABASE 
61 0 0x0086 BACKUPBUFFER                       BACKUP DATABASE 
61 0 0x0087 BACKUPIO                           BACKUP DATABASE 
62 61 0x0004 LCK_M_U                          DB: 16 [BULKOP_BACKUP_DB] BACKUP DATABASE (this is the differential backup)
68 0 0x0087 BACKUPIO                           BACKUP LOG      
68 0 0x0087 BACKUPIO                          BACKUP LOG      

Output from FULL backup

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Processed 17512 pages for database 'mydb', file 'mydbData' on file 1.

Processed 1 pages for database 'mydb', file 'mydbLog' on file 1.

BACKUP DATABASE successfully processed 17513 pages in 18.986 seconds (7.206 MB/sec).

SQL Server Execution Times:

   CPU time = 70 ms,  elapsed time = 20256 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Output from DIFFERENTIAL backup

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Processed 32 pages for database 'mydb', file 'mydbData' on file 1.

Processed 1 pages for database 'mydb', file 'mydbLog' on file 1.

BACKUP DATABASE WITH DIFFERENTIAL successfully processed 33 pages in 0.321 seconds (0.801 MB/sec). <-- actual time for the differential backup to complete

SQL Server Execution Times:

   CPU time = 51 ms,  elapsed time = 19777 ms.   <-- total time to execute the command, including wait and runnable time

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Note that the time taken by the command is just 0.321 seconds but the elapsed time is 19.777 seconds, which clearly demonstrates the blocking.

Output from LOG backup

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Processed 3 pages for database 'mydb', file 'mydbLog' on file 1.

BACKUP LOG successfully processed 3 pages in 7.958 seconds (0.002 MB/sec).

SQL Server Execution Times:

   CPU time = 60 ms,  elapsed time = 15106 ms.

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

Time taken by command is around 52% of time elapsed, which is probably due to the fact that we are on a single-CPU system and the task had to wait for runnable time on the CPU.

Summary

Here is the summary of the testing I conducted. I hope the summary is useful to all of you.

 

What is requested?

What is already running? (rows below)

Complete

Differential

Log

Complete backup is running

Blocks in all versions *

Blocked in all versions *

Concurrently allowed in SQL 2005 / 2008. Not allowed in 2000.

Differential backup is running

Blocks in all versions *

Blocked in all versions *

Concurrently allowed in SQL 2005 / 2008. Not allowed in 2000.

Log backup already is running

Concurrently allowed in SQL 2005 / 2008.

Concurrently allowed in SQL 2005 / 2008.

Blocked in all versions *

 

* Note: wait type of the blocked process will be LCK_M_U with a waittype of BULKOP_BACKUP_DB