July 13, 2015 at 8:16 am
We have a number of reports scheduled to run throughout the week. Some reports are also setup as Agent Job reports and use SP_send_dbmail to the send the report in HTML format. For the last week or so, multiple reports have failed to send, while others are going through in the same day. IT's been an intermitten issue as some days all reports seem to go through, but i don't have any alerts to notify me of failed reports.
I looked through the log files for the reportserver from \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles ; This error messages comes up frequently:
ERROR: PollingMaintenance: Restarting maintenance thread for the following exception: This method or property cannot be called on Null values.
at System.Data.SqlClient.SqlBuffer.get_String()
at Microsoft.ReportingServices.Library.InstrumentedSqlDataReader.<>c__DisplayClass3d.<GetString>b__3c()
at Microsoft.ReportingServices.Library.SqlBoundaryWithReturn`1.Invoke(Method m)
at Microsoft.ReportingServices.Library.EventQueueWorker.GetNextQueueItem(IDataRecord record)
at Microsoft.ReportingServices.Library.QueuePollWorker.ProcessData(IDataReader reader)
at Microsoft.ReportingServices.Library.DBPoll.PollingFunction()
at Microsoft.ReportingServices.Library.DBPoll.PollingMaintenance().
library!WindowsService_827!8d4!07/10/2015-05:54:19:: i INFO: PollingMaintenance: Polling cycle completed.
Other than that, i'm not seeing any useful info in the ExecutionLogStorage table under LastStatus ( just says rsProcessAborted on a few records).
I also tried running this query to look for failed reports, but the ones that failed today aren't coming up:
SELECT
c.Name AS [ReportName],
sb.[Description] AS [SubscriptionDescription],
sb.DeliveryExtension AS [DeliveryType],
sb.LastStatus AS [LastRunStatus],
sb.LastRunTime AS [LastRunTime],
c.Path AS [ReportPath],
'http://sql-server/Reports/Pages/Report.aspx?ItemPath='+REPLACE(REPLACE(C.[Path],'/','%2f'),' ','+')+'&SelectedTabId=PropertiesTab&ViewMode=List&SelectedSubTabId=SubscriptionsTab' AS [SubscriptionLink],
sc.ScheduleID AS [SQLAgentJobName],
sb.SubscriptionID
FROM
ReportServer.dbo.ReportSchedule AS RS
INNER JOIN ReportServer.dbo.Schedule sc ON rs.ScheduleID = sc.ScheduleID
INNER JOIN ReportServer.dbo.Subscriptions sb ON rs.SubscriptionID = sb.SubscriptionID
INNER JOIN ReportServer.dbo.[Catalog] c ON rs.ReportID = c.ItemID AND sb.Report_OID = c.ItemID
WHERE
(sb.LastStatus LIKE 'Failure%' OR sb.LastStatus LIKE 'Error%' OR sb.LastStatus LIKE '%not valid%')
order by lastruntime asc
July 15, 2015 at 9:53 am
This got resolved with the help of a consultant from our cloud hosting providers;
He found the solution from articles i actually came accross but didn't read fully so i didn't find the communality with my issue...It turned out to be a bunch of orphaned events that were logged in the Events table, but weren't getting processed. Overtime it seemed to have built up to the point that RS Wasn't able to either create new events or even subscriptions in some cases.
I don't have the full detail as to how the consultant fully resolve the issue, but these articles really cover the issue well and also help identifying failed events in the RS logfile, which i couldn't really make sense of at first either.
http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx
http://blogs.msdn.com/b/psssql/archive/2009/02/02/why-aren-t-my-subscriptions-working.aspx
This was a pretty cool exercise that lead me to much better understand how RS Works are what the different processes that occur when a subscription report is executed. Also learned how to query the different RS tables to get the required info. If anyone would like to add feel free!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply