To Capture a Deadlock Graph using Profiler (NB: with SQL 2008 and above you can also use an extended-event).
- File / New trace
- Connection details
- Use Template / Blank
- Events Selection / Locks ..1) DeadlockGraph 2) Lock:Deadlock 3) Lock:Deadlock Chain
- Event Extraction Settings / Save Deadlock XML events seperately / (somefilename)
- Each deadlock in a distinct file
- All Deadlocks
- File / Export / Extract SQL Server Events / Extract deadlock Events / (somefilename2)
I noticed a log-shipping RESTORE job had started failing. Looking back through the job history I found the last two “good” executions contained errors …
*** Error: Could not apply log backup file ‘SomePath\SomeFile.trn’ to secondary database. The volume is empty.
I looked at the path\file specified and found the file was zero size.
I looked on the network-share where the files are backed-up-to \ copied-from, and found the same file was NOT zero size.
I manually copied the file from the network-share to the destination folder (on the DR server), overwriting the empty file.
Log-shipping recovered over the next few hours.