Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Database backup does not cause blocking

08 February,2013 by Jack Vamvas

Question: Does a SQL Server database backup cause blocking of user transactions?  We notice during a backup run that transactions become very slow and even unresponsive.

Answer :  A SQL Server database backup  does not cause locks on user objects. Therefore it cannot cause blocking on other transactions .  A SQL Server database backup is an ONLINE backup.

Use this script to identify SQL Server Locks - SQL Server – Find sql server Locks

More investigation is required to identify the source of slow response or application timeouts

Some problems during a database backup include:

1) A database backup operation requires disk reads. This can lead to IO pressure. Also,   depending on the backup file target  IO pressure can be higher. Read more on SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

Depending on the IO throughput capacity and response times of the IO subsystem , IO pressure can transactions to wait on backup read operations

2) Read and writing the same disk can cause IO slowdown. If there are a low amount of spindles this can cause further issues

Scheduling database backup operations during  a backup window allows DBAs to complete database backups meeting the organizations Recovery Points. This minimizes the type of contention outline above.  In a 24 x 7 operation , where fast response times are required throughout the day, less instrusive methods of backup are requires such as block level flash copy

Read More

SQL Server -Error 3041 - Backup failed to complete the command BACKUP DATABASE WITH DIFFERENTIAL

SQL Server – Predict the database backup size

SQL Server - Display sql backup history for a single database

Calculate disk IO throughput and MB per second - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer