bug: data-driven subscription generates deadlock in ReportServer D

I've got a report that can have about 50 possible parameter combinations. The
underlying query is fairly expensive, so I want to cache the dataset every
morning after I expire yesterday's cache. So I set up a data-driven
subscription to run every morning and execute all the parameter combinations
and deliver it to the Null delivery extension. The problem is that when the
scheduled time rolls around, it tries to run all 50 parameter combinations at
once. (Actually, it looks like it runs 2 or 3 combinations a second without
waiting for the first one to finish.) The query of the data sources run fine,
but I get a deadlock when Reporting Services tries to write the dataset to
the cache. Somewhere between 10% and 50% of the 50 fail every day. The logs
show a standard deadlock error. The error is below.
Is there any config file setting to tell RS to retry writing to the cache a
few times before giving up because of a deadlock? Is this possibly a SP2 fix?
Or is there any way to tell Reporting Services to run the 50 executions
serially instead of concurrently? Or do I need to investigate creating some
custom code to execute and cache the reports on schedule (so I can make it
execute them serially)?
Please let me know if you need more info... or if you need something from
the SQL Server logs. Thanks in advance.
--
ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
132) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Guid
reportId, ReportSnapshot snapshotData, DateTime executionDateTime, DateTime&
expirationDateTime)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
-- End of inner exception stack trace --Hi,
Try setting MaxQueueThreads to 1 in the RSReportServer.config file. You may
want to escalate this to the RS PSS since it looks like a bug to me.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:516B456B-0E41-46AA-B775-D3BA1B13C1F0@.microsoft.com...
> I've got a report that can have about 50 possible parameter combinations.
The
> underlying query is fairly expensive, so I want to cache the dataset every
> morning after I expire yesterday's cache. So I set up a data-driven
> subscription to run every morning and execute all the parameter
combinations
> and deliver it to the Null delivery extension. The problem is that when
the
> scheduled time rolls around, it tries to run all 50 parameter combinations
at
> once. (Actually, it looks like it runs 2 or 3 combinations a second
without
> waiting for the first one to finish.) The query of the data sources run
fine,
> but I get a deadlock when Reporting Services tries to write the dataset to
> the cache. Somewhere between 10% and 50% of the 50 fail every day. The
logs
> show a standard deadlock error. The error is below.
> Is there any config file setting to tell RS to retry writing to the cache
a
> few times before giving up because of a deadlock? Is this possibly a SP2
fix?
> Or is there any way to tell Reporting Services to run the 50 executions
> serially instead of concurrently? Or do I need to investigate creating
some
> custom code to execute and cache the reports on schedule (so I can make it
> execute them serially)?
> Please let me know if you need more info... or if you need something from
> the SQL Server logs. Thanks in advance.
> --
> ReportingServicesService!library!2e72c!10/28/2004-21:25:21:: e ERROR:
> Throwing
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details., ;
> Info:
>
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
more
> details. --> System.Data.SqlClient.SqlException: Transaction (Process ID
> 132) was deadlocked on lock resources with another process and has been
> chosen as the deadlock victim. Rerun the transaction.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
> at
>
Microsoft.ReportingServices.Library.DBInterface.AddReportToExecutionCache(Gu
id
> reportId, ReportSnapshot snapshotData, DateTime executionDateTime,
DateTime&
> expirationDateTime)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(Catalog
ItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters)
> at
>
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
> reportContext, ClientRequest session, Warning[]& warnings,
> ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
> -- End of inner exception stack trace --
>|||That did it. Thanks SO much for your response. Now the executions of each
individual parameter combination run serially and don't cause deadlocks.
I did some research on that config file setting you suggested, Teo. Here's
what I found:
MaxQueueThreads
Specifies the maximum number of threads dedicated to polling the event table
in the report server database.
Range: 0 to max integer.
The default is 0.
So if I understand correctly, this setting will ONLY affect the number of
threads used to process events like the fulfilling of subscriptions. Is that
correct? It will not impact performance of multiple users executing and
viewing reports on demand and at the same time, right?|||This is my understanding as well but verify it using the Performance
Monitor.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:C9EF39EE-1638-47E9-AE5F-24896700A73D@.microsoft.com...
> That did it. Thanks SO much for your response. Now the executions of each
> individual parameter combination run serially and don't cause deadlocks.
> I did some research on that config file setting you suggested, Teo. Here's
> what I found:
> MaxQueueThreads
> Specifies the maximum number of threads dedicated to polling the event
table
> in the report server database.
> Range: 0 to max integer.
> The default is 0.
> So if I understand correctly, this setting will ONLY affect the number of
> threads used to process events like the fulfilling of subscriptions. Is
that
> correct? It will not impact performance of multiple users executing and
> viewing reports on demand and at the same time, right?

Bug found when updating a one-time scheduled subscription...

When you first create a data-driven sub and schedule it to run "one time" it
works fine. Later, however, if you edit it to try and re-run it, you run
into a problem if the subscription is set to run on a one-time basis only
again. You select "one time" as the schedule and set the time you want it to
fire off. However you can't enter a date. Instead of picking up today as
the default like it does when you first create the subscription, it keeps the
same date that you originally used when you first set it up. So if you are
editing a one-time scheduled subscription and you didn't create the
subscription that same day, the schedule you just created is already in the
past and thus never fires. You can confirm this by checking the "startdate"
field of the "schedule table". I tried working around it by updating the
table manually...
Update Schedule
Set StartDate = '2004-11-10 14:10'
Where ScheduleID = 'whatever'
... but it still didn't fire off the job. Anyone else seen this?
sebring1130you can use the soap api and call rs.firevent on it
"sebring1130" wrote:
> When you first create a data-driven sub and schedule it to run "one time" it
> works fine. Later, however, if you edit it to try and re-run it, you run
> into a problem if the subscription is set to run on a one-time basis only
> again. You select "one time" as the schedule and set the time you want it to
> fire off. However you can't enter a date. Instead of picking up today as
> the default like it does when you first create the subscription, it keeps the
> same date that you originally used when you first set it up. So if you are
> editing a one-time scheduled subscription and you didn't create the
> subscription that same day, the schedule you just created is already in the
> past and thus never fires. You can confirm this by checking the "startdate"
> field of the "schedule table". I tried working around it by updating the
> table manually...
> Update Schedule
> Set StartDate = '2004-11-10 14:10'
> Where ScheduleID = 'whatever'
> ... but it still didn't fire off the job. Anyone else seen this?
> sebring1130
>
>