1. Support
  2. Knowledge base
  3. 100021898

SQL Deadlocks during Enterprise Vault background tasks

Article: 100021898
Last Published: 2021-10-01
Ratings: 0 0
Product(s): Enterprise Vault

Problem

SQL deadlocks during Enterprise Vault background tasks.

Cause

A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource. Although deadlocks can be minimized, they cannot be completely avoided. The more actions a SQL server processes, the higher the possibility a deadlock will occur.

Enterprise Vault has been designed with several trawling background tasks that manipulate tables. Storage expiry, folder delete and storage file watch are examples of trawling background tasks. Enterprise vault is designed to recover from deadlock events.

Though deadlocks are common during execution of certain background tasks, an increased number of deadlock victims can cause latency. In this event, actions should be considered to reduce deadlock victims.
 

Solution

Actions that can reduce deadlock victims
  • Perform regular SQL maintenance on the Enterprise Vault (EV) databases according to the procedures outlined in Related Article: 000040169
  • Increase the amount of RAM on the SQL server
  • Balance active databases between multiple SQL servers
  • Limit the amount of different programs that a SQL server processes
Note: Set EV services into backup mode when performing maintenance. Compliance and Discovery Accelerator databases require the Enterprise Vault Accelerator Manager Service to be stopped during maintenance.


To monitor the SQL server for deadlocking issues

Start and monitor a SQL Profiler while performing daily activities.
 
 
1. Open SQL Server Profiler
2. Select File then New Trace.
3. Use a Blank Template and expand Locks.
4. Select Lock:Deadlocks and Lock:Deadlock Chains.
5. Run


To collect data for troubleshooting deadlocks

1. Set the SQL server to capture deadlock information by running the following query through the Query Analyzer:
 
DBCC TRACEON (1204, 1205, 3605, -1)
GO
DBCC TRACESTATUS(-1)
GO

2. Collect a SQL profiler trace
 
  • Select File, then New Trace.
  • Use a Blank Template and expand Locks.
  • Save the trace to file.
  • Select Lock:Deadlock graph, Lock:Deadlock and Lock:Deadlock Chains.
  • Under the Events Extraction Settings, select Save Deadlock XML events separately.
  • Run

3. After a deadlock occurs, process the SQL diagnostic information into an output file through a command prompt.
 
SQLDiag /O "C:\SQLDiagOutput"


4. Gather the Profiler trace, Deadlock Graph and SQLDiagOutput files for analysis.


For additional information on how to analyze deadlock data, refer to the Microsoft knowledge base article:
https://support.microsoft.com/kb/832524

Was this content helpful?

Article Languages