Differential Backup Blocking

• sqlnaive

  SSCoach

  Points: 17437

  More actions

  March 22, 2012 at 12:59 am

  #266081

  Can a differential backup block any batch or make it wait for long time then usual ? Though I have read in BOL that "SQL Server uses an online backup process to allow a database backup while the database is still in use. During a backup, most operations are possible; for example, INSERT, UPDATE, or DELETE statements are allowed during a backup operation."

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  March 22, 2012 at 2:30 am

  #1462546

  BACKUP is not a blocking operation. It consumes CPU and I/O and could slow down the instance, but not block other processes.

  Paul Randal has a nice blog post on Backup Myths here: http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(3030)-backup-myths.aspx

  To discover what is really blocking, use the Activity Monitor or Adam Machanic's sp_WhoIsActive.

  Hope this helps

  Gianluca

  -- Gianluca Sartori