Locks taken by SQL Server

• coolchaitu

  SSChampion

  Points: 10334

  More actions

  November 4, 2023 at 1:10 pm

  #4321126

  Which locks are taken by SQL Server during database restore? Is it schema modification lock?

• Eddie Wuerch

  SSChampion

  Points: 12538

  More actions

  November 4, 2023 at 3:53 pm

  #4321197

  1. Kick off a restore
  2. SELECT * FROM sys.dm_tran_locks WHERE request_session_id = the @@SPID of the restoring connection.

  During restore, the restoring thread will take an X lock on the whole database. Restore just directly repaints the files that were backed up. Database recovery will start after the files are restored if RESTORE WITH RECOVERY was called. Post-restore recovery is just normal start-up 3-phase recovery; locks are taken during UNDO/REDO as needed.

  Eddie Wuerch
  MCM: SQL

• coolchaitu

  SSChampion

  Points: 10334

  More actions

  November 5, 2023 at 6:16 am

  #4321500

  Thanks Eddie. I read somewhere that it takes Schema-Modification lock.Is this true?