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