December 12, 2019 at 2:32 am
we have a SQL server 2017 with reportinig service installed. The database engine only hosts reportDB and reportTempdb. We have a SSRS complicated report querying oracle database. The report generates PDF version too. The report calling from report viewer from a web page.
Since this week is the peak time of the report, but many report errored out.
From the SQL server performance dashboard, there are frequent locks and blocks.
I also see in our Database monitoring tool the top wait query is this one:
Procedure: ReportServer.dbo.CheckSessionLock
Character Range: 134 to 273
Waiting on statement:
SELECT @Selected=SessionID,
@LockVersion = LockVersion FROM [ReportServerTempDB].dbo.SessionLock
WITH
(
ROWLOCK
)
WHERE SessionID = @SessionID
*/
CREATE PROCEDURE [dbo]
.[CheckSessionLock]
@SessionID
as
varchar(32),
@LockVersion int OUTPUT
AS
DECLARE
@Selected nvarchar(32)
/* BEGIN ACTIVE SECTION (comment inserted by DPA) */
SELECT @Selected=SessionID,
@LockVersion = LockVersion FROM [ReportServerTempDB].dbo.SessionLock
WITH
(
ROWLOCK
)
WHERE SessionID = @SessionID
/* END ACTIVE SECTION (comment inserted by DPA) */
e
I see some online forum user enabled READ_COMMITTED_SNAPSHOT for ReportServerTempDB revolved the problem. I want to try that too.
But I am not sure which database should I enable the above isolation level? the reportserver or reportserverTempdb.
December 13, 2019 at 3:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply