KBI 310273 SQL Server Deadlocks Running Automatic Reports

Version

All

Date

16 Nov 2011

Summary

Very long running automatic distribution reports that run frequently can cause stress on SQL Server and deadlock errors in logs:

Symptoms: Long delays in running Relators and firing alerts.

Logs:

11 Nov 2011 ::: AppArgentService Argent Guardian automatic report distribution: Finished one chart graph file ‘RPT_OPUS2_ERRORLOG_NY_TOTAL’ (369.92 seconds)

This report takes over five minutes to be generated but is scheduled to run every five minutes


Deadlock error:

SQL query encounters ODBC error (Transaction (Process ID 161) was 

          deadlocked on lock resources with another process and has been chosen 

          as the deadlock victim. Rerun the transaction. 

           ). SQL operation is to be retried in 10000 ms. (FILE: AG_subs.cpp 

          LINE: 1139)

Technical Background

Fairly obvious — report is scheduled to run in less time that the frequency (takes seven minutes to generate, but is run every five minutes)

SQL Server is overloaded and Microsoft’s SQL Server suffers deadlocks.

Like running no-stop SELECT query without WHERE clause in a million-row table.

This can cause SQL not to response other queries in timely fashion.

So some queries to SQL may get deadlock errors.

Resolution

Correct Relator