August 21, 2012 at 8:25 am
SQL 2005 with Reporting Services on another server.
For the past week, subscriptions are not generating any output. Nothing has changed in the reports, on either server, or with the accounts being used throughout the process. Each of the machines has been in use for over a year without incident. Services (SQL and Reporting) have been restarted with no impact.
Searching through sysjobs, sysjobschedules, and sysschedules I can see that the jobs ARE being created with enabled schedules. However, when looking in sysjobactivity and sysjobhistory there is no indication that the job even attempted to start. I've manually added a schedule via sp_add_schedule and sp_attach_schedule with the only differences being the GUID and the start time. That schedule kicked off without any problem. I can also use sp_start_job to manually kick it off without problem. It's just the subscription generated schedules that are failing.
There is nothing in the event logs that reference these jobs. What I do have are multiple entries to the effect of
[000] Failed to retrieve job 0x1E8EBF3407D02C49AFE44E4D2EB7562B from the server
[156] Job 0x1E8EBF3407D02C49AFE44E4D2EB7562B does not exist in the job cache: attempting to re-acquire it from the server...
[298] SQLServer Error: 2601, Cannot insert duplicate key row in object 'dbo.sysjobactivity' with unique index 'clust'. [SQLSTATE 23000]
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000]
[000] Failed to retrieve job 0x1E8EBF3407D02C49AFE44E4D2EB7562B from the server
[000] Refresh of schedule 95721 failed because the parent job (0x1E8EBF3407D02C49AFE44E4D2EB7562B) was not found in the job cache
I tracked back a couple of the schedules and found both had a command like
exec ReportServerBatch.dbo.AddEvent @EventType='TimedSubscription', @EventData='e1df527f-f84c-4928-b6da-e4e4d456db0c'
Does anyone have any suggestions? I've spent 2+ days googling various parts of the error messages and finding zero help.
Thanks in advance!
August 21, 2012 at 8:37 am
Total stab in the dark, but are the subscriptions using shared schedules?
Could it be that the subscription have expired, but due to the schedule being shared, the schedule doesnt get disabled, but the subscription stops sending.
August 21, 2012 at 8:47 am
Nope. New subsciptions with new schedules.
August 21, 2012 at 8:49 am
And the subscription schedules dont have the "stop this schedule on" value set?
As for shared schedules, the same applies, the whole schedule stops not just individual subscriptions against that schedule, so it ruled my original point out.
August 21, 2012 at 9:17 am
ReportServer.dbo.Subscriptions enddate value is null for whole table
msdb..sysschedules active_end_date is '99991231' for whole table
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply