HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Deadlock on Report server database

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
deadlocksqlsql-serverstackoverflowdba
deadlockreportdatabaseserver

Problem

Getting number of deadlocks from ReportServer.dbo.ExpireExecutionLogEntries storedproc. I spent my whole day on watching tutorials on deadlock but could not figure out this.

```








delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'


Proc [Database Id = 9 Object Id = 1143675122]




delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'


Proc [Database Id = 9 Object Id = 1143675122]




delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'


Proc [Database Id = 9 Object Id = 1143675122]




delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'


Proc [Database Id = 9 Object Id = 1143675122]




delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'


Proc [Database Id = 9 Object Id = 1143675122]




delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'


Proc [Database Id = 9 Object Id = 1143675122]




delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'


Proc [Database Id = 9

Solution

The relevant bit is here:


      
        
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    
      
      
Proc [Database Id = 9 Object Id = 1143675122]   
    
    
      
        
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) >= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    
      
      
Proc [Database Id = 9 Object Id = 1143675122]


Two SSRS servers SERVER1, and SERVER2 are running this proc simultaneously. SERVER2 is using a parallel plan, which accounts for all of the other blocked processes, but they all belong to spid 115.

Not sure if all the ssrs front-end server are supposed to run this procedure at the same time, so you might try updating to the latest version.

But at the end of the day, this is probably no worse than an annoyance and a waste of resources. One of the sessions successfully completed the cleanup.

You can always configure SSRS to keep the ExecutionLog entries indefinitely, and schedule an Agent job to run ExpireExecutionLogEntries on a schedule of your choosing.

Code Snippets

<process id="process1ea5e987088" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419598" ownerId="10654834267" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.513" XDES="0x1f3d5876ca0" lockMode="U" schedulerid="10" kpid="17232" status="suspended" spid="115" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.513" lastbatchcompleted="2021-08-14T01:59:59.513" lastattention="1900-01-01T00:00:00.513" clientapp="Report Server" hostname="VM-AE-SQL-2" hostpid="4180" isolationlevel="read committed (2)" xactid="10654834267" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
    </process>
    <process id="process1e5eb1904e8" taskpriority="0" logused="0" waitresource="PAGE: 9:1:288319 " waittime="419669" ownerId="10654834619" transactionname="DELETE" lasttranstarted="2021-08-14T01:59:59.757" XDES="0x1f49a45b900" lockMode="U" schedulerid="11" kpid="18024" status="suspended" spid="88" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2021-08-14T01:59:59.757" lastbatchcompleted="2021-08-14T01:59:59.757" lastattention="1900-01-01T00:00:00.757" clientapp="Report Server" hostname="VM-AE-SQL-1" hostpid="24648" isolationlevel="read committed (2)" xactid="10654834619" currentdb="9" currentdbname="ReportServer" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="ReportServer.dbo.ExpireExecutionLogEntries" line="11" stmtstart="512" stmtend="886" sqlhandle="0x03000900f2182b44a41004007dab000001000000000000000000000000000000000000000000000000000000">
delete from ExecutionLogStorage
where DateDiff(day, TimeStart, getdate()) &gt;= (select CAST(CAST(Value as nvarchar) as integer) from ConfigurationInfo where [Name] = 'ExecutionLogDaysKept'    </frame>
      </executionStack>
      <inputbuf>
Proc [Database Id = 9 Object Id = 1143675122]   </inputbuf>
    </process>

Context

StackExchange Database Administrators Q#298052, answer score: 6

Revisions (0)

No revisions yet.