I have 5 reports that utilize a shared schedule to generate report snapshots on a nightly basis. Each of these reports is emailed to a distribution group when the corresponding report snapshot has been generated (trigger SnapshotUpdated) using a subscription. So far, these reports have been running without issue until recently. The other night, one of the 5 reports failed to email to the distribution group. Referencing the ReportServerService.log file for that time period I clearly see the following error message: Error processing event 'SnapshotUpdated', data = 4ca4fd05-ed20-491b-897e-e2cf28c08bc2, error = System.Data.SqlClient.SqlException: Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. So, the SnapshotUpdated event was not successfully written to the database, and thus, no email. Simply staggering the report execution would probably resolve the issue, however, I'm curious if this is a "bug". Microsoft SQL Server Reporting Services Version 9.00.4053.00 Any information or assistance would be greatly appreciated. Sincerely, Sean Fitzgerald

Hi, Based on your descriptioin, this issue is caused because of deadlock in reporting services. To solve this issue, i would suggest you try the following options: 1. If possible, configure report services to subscribe these report at different time with a certain interval. 2. Increase the max times of retries if one of report deliveries fails. To do this, go to the file rsreportserver.config, and locate these elements to update their values to try: <MaxRetries>3</MaxRetries> <SecondsBeforeRetry>900</SecondsBeforeRetry> The good news is that this issue is corrected in SSRS 2008. thanks, Jerry

Thanks for the response Jerry. I was hoping that this would also be fixed in SSRS 2005. Fortunately, my organization has plans to move to SSRS 2008 in the near future. I haven't seen the issue since the last occurrence, but if necessary I will stagger the report subscriptions so that they are generated at different times. I'm not clear that increasing the max times of retries if one of the report deliveries fails to >3 is a solution, as my report delivery didn't fail. The trigger (a snapshot was generated) failed to write to the database due to the deadlock, so the report subscription was never fired. Regardless, thank you for your feedback. Sincerely, Sean Fitzgerald

Report Server procedures: PROCEDURE [dbo].[CreateSnapShotNotifications] ...from [Subscriptions] S with (READPAST) inner join History H on S.[Report_OID] = H.[ReportID]... from [Subscriptions] S with (READPAST) inner join History H on S.[Report_OID] = H.[ReportID]... PROCEDURE [dbo].[CreateCacheUpdateNotifications] ...from [Subscriptions] S inner join Catalog C on S.[Report_OID] = C.[ItemID]...from [Subscriptions] S inner join Catalog C on S.[Report_OID] = C.[ItemID]... So the solution to deadlocking on subscriptions is to add with(READPAST) to [dbo].[CreateCacheUpdateNotifications] just like dbo.[CreateSnapShotNotifications]. IE "from [Subscriptions] S with (READPAST) ..." in both places in the proc. You shouldn't need to resolve this by changing configuration files for retries, or for scale out settings (IsSchedulingService, IsNotificationService, ISEventService) on 1 server to False, or reducing max queue threads to 1. You should resolve this by fixing the underlying source of the deadlock...at the proc level. Note until MSFT fixes this proc, you will need to reapply this change every time you do an update to SQL Server versions. MSFT while you are at it. consider whether or not " inner join Catalog C on S.[Report_OID] = C.[ItemID] where C.[ItemID] = @ReportID" is better served by "where S.[Report_OID] = @ReportID and S.EventType... If there is no C in the select statement why does the proc join to C?