Post reply

Locks taken by SQL Server

  • November 4, 2023 at 1:10 pm

    #4321126

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

  • 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

  • November 5, 2023 at 6:16 am

    #4321500

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply