SSRS Subscription run causing locking of the tables and results in SQL Agent job failure

I have one SSRS report which has been scheduled to run first 10 days of every month and has been in place since 2015 but from last week onwards, the scheduled report subscription run had been taking around 2 hours to complete processing which normally take max 10 minutes.

this is also causing issues with the SQl Agent job failure as the table which are being queried by the report run are getting locked and the SQL Agent job is unable to access those tables and getting failed with Time Out error. when Subscription run is completed, all the jobs run within the time without any errors.

I had checked the data set queries for the report, and in some of the data set queries there are WITH(NOLOCK) options defined with the Table definition but not in all the data set queries.

I am not really looking for defining the WITH(NOLOCK) options in every data set as this gives dirty reads but any locking strategy that can be used in SSRS Report which can prevent the SQL agent jobs to fail when subscription runs.

i even tried enabling READ_COMMITTED_SNAPSHOT ON on the database where my tables are present and are being queried but this resulted in the Subscription processing errors though the SQL Agent jobs didn't fail during the subscription run.

i am looking for any lock mode that can be set at the query level or on the connection string or any other option. NOLOCK is not necessarily the answer for this, although good for throughput, it uses dirty reads which can produce inconsistent data.

Any suggestion around this?

more ▼

asked Aug 10, 2016 at 07:48 AM in Default

avatar image

51 3 4 9

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 10, 2016 at 07:48 AM

Seen: 55 times

Last Updated: Aug 10, 2016 at 07:48 AM

Copyright 2018 Redgate Software. Privacy Policy