SQL Deadlock log collection

KB ID: 2111
Products: Veeam Backup & Replication
Version: All
Published:
Last Modified: 2016-03-28

Challenge

To gather logs for SQL Deadlock troubleshooting.

Cause

DB actions can in certain circumstances cause deadlock issues.

Solution

Please ensure that you have SQL Server Management Studio Tools (SSMS) installed on the Veeam computer. http://www.veeam.com/kb1761 references where to download SSMS. Install the most recent version as it is backward compatible with older versions of SQL while older instances of SSMS are not compatible with newer versions of SQL.
 
For SQL versions 2012+:
1. Connect to the Veeam SQL DB. The default connection parameter for Veeam Backup and Replication version 8 and 9 is localhost\VEEAMSQL2012.
2. Expand Management > Extended Events > Sessions > System health
3. Right click package0.event_file and select "View Target Data"

User-added image

4. Select "Filters..."

User-added image

5. Click on "click here to add a clause" then enter the values as listed below:
Field = name, Operator = Contains, Value = deadlock

User-added image

User-added image

6. Click "Apply", then click "OK"
7. Right-click the most recent deadlock and select "Copy > Details"

User-added image

8. Send a copy of the resulting data Veeam support
 
For SQL versions before 2012:
1. Connect to the Veeam SQL DB
The default connection parameter for Veeam Backup and Replication version 8 and 9 is localhost\VEEAMSQL2012.
2. Run the following query on the Veeam database, "DBCC TRACEON (1222, -1)".
3. Recreate the errors in Backup and Replication.
4. Collect all “ERRORLOG” files from the MS SQL server instance
By default the SQL instance logs are on the Veeam computer with the path “C:\Program Files\Microsoft SQL Server\MSSQL10_50.VEEAMSQL2008R2\MSSQL\Log”.
5. Generate a backup of the Veeam database as per https://www.veeam.com/kb1471
6. Generate a Veeam log bundle as per https://www.veeam.com/kb1832
7. Send the database errors logs, database backup, and Veeam logs to Veeam support for review

5 / 5 (1 votes cast)

Report a typo on this page:

Please select a spelling error or a typo on this page with your mouse and press CTRL + Enter to report this mistake to us. Thank you!

Orphus system