Capturing Deadlock Processes through Database Restore

  •  

    I'd like to know if its possible for SQL to capture deadlock information (using DBCC Trace 1204,1205) when restoring a database using both full backup and a series of transaction logs.  I've backed up a database that contains production deadlocking events.  By applying the transactions logs up to the time deadlocking has happen ontop of the full backup, will SQL capture and write this information to the SQL logs?

    I'm using this method to see if making various Hardware and SQL configurations will help out.

    Appreciate responses

  • No, deadlock information cannot be detected by applying the transactions logs after a restore because only successfull events are written to the log and unsuccesfull events are not written to the log.

    Some alternative are to run a trace capturing the SQL or monitor the processes.

    For monitoring the processes, see Erland Sommarskog's Web page

    at http://www.sommarskog.se/sqlutil/aba_lockinfo.html

    SQL = Scarcely Qualifies as a Language

  • Hi, as previous reply, we use aba_lockinfo very successfully. 

    http://www.sommarskog.se/sqlutil/aba_lockinfo.html

     Rgds

    Derek

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

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