We frequently see blocking on our SSRS box which houses both front-end Report Manager ReportingService and the SQL database engine with ReportServer & ReportServerTempDB Catalog databases
Blocking session:
Database name: ReportServer
Application name: Report Server
Host name: SSRS01
OS User name: SYSTEM
Database User name: NT AUTHORITY\SYSTEM
First 2KB of SQL: CREATE PROCEDURE [dbo].[CheckSessionLock] @SessionID as varchar(32), @LockVersion int OUTPUT AS DECLARE @Selected nvarchar(32) SELECT @Selected=SessionID, @LockVersion = LockVersion FROM [ReportServerTempDB].dbo.SessionLock WITH (ROWLOCK) WHERE SessionID = @SessionID
Blocked session:
Database name: ReportServer
Application name: Report Server
Host name: SSRS01
OS User name: SYSTEM
Database User name: NT AUTHORITY\SYSTEM
First 2KB of SQL: CREATE PROCEDURE [dbo].[CheckSessionLock] @SessionID as varchar(32), @LockVersion int OUTPUT AS DECLARE @Selected nvarchar(32) SELECT @Selected=SessionID, @LockVersion = LockVersion FROM [ReportServerTempDB].dbo.SessionLock WITH (ROWLOCK) WHERE SessionID = @SessionID
Anyone else experience this, and fix it ?