Our implementation of MSRS requires the use of the non-integrated (Native) model, as our reports are viewed through secure portals to the WWW. RS Native installed on SQL Server 2008 (R2). This implementation will replace an existing Crystal Enterprise Backoffice-driven system that retrieves report requests from various sources and schedules the reports after hours. The volume handled by this system is very high - in excess of 10,000 reports per month, with spikes as high as 40,000 reports at quarter and year-end. The results of the reports must be retained for up to 2,600 days (Dept of Labor mandate), so we're building snapshots. The end users require export to alternate formats (PDF, CSV, XLS) from the viewing portals, so snapshots are our only alternative. Please do not attempt to re-design our approach to the implementation - all I'm interested in is how to best use the system to attempt what's previously defined, and when things don't go as planned, what auditing tools/data are available to sleuth out the true cause of the problem. Standard report set up to return snapshot, with options to retain all snapshots in history. Scheduled snapshot to run at 7:51am today. Viewed RunningJobs table in SQLSvr and report appeared in list around 7:51. Report is set to never time out. Same report, when executed in CE takes up to 3 hours to complete. Some time around 8:45am, the entry for the report disappeared from the RunningJobs table. Checked the ExecutionLogStorage table, expecting to see the results of the report run - no records found. Checked the Schedule table, and the entry for the 7:51 run was still present (query only returns records where LastRunTime is null). Data source for report is an Oracle 9i database. Checked the v$Session table for the ID used by the RS Data Connection and saw an entry that corresponds to the start time of the schedule. Waited 20 or more minutes. Report never reappeared in RunningJobs. Report never disappeared from Schedule table. Had Oracle DBA kill session. Immediately, ExecutionLogStorage table has an entry for the report, showing TimeStart = 7:48:28 (why would the actual start time precede the requested time in the schedule?), and TimeEnd = 8:56:04am, with Status = rsProcessingAborted. Initially, I attempted to schedule reports by setting the default parameters, then going to the History screen and clicking the New Snapshot button. I'd leave that IE tab active as long as it took the report to run. On more than one occasion, after a period of time which seemd to vary, the tab would reload with "IE Cannot Display the Web Page" message. We thought that maybe this was a result of IIS timing out the page after a period of time and that was causing RS to abandon the report request. In these situations, the snapshots never appeared in the Report History screen, and only when the DBA's would kill the underlying Oracle sessions (or the sessions would die when the database gets refreshed at 8pm) would entries appear in the ExecutionLogStorage table. I worked most of yesterday using the Snapshot Schedule option to request a number of reports, some of which ran in just a few seconds, others which took several hours. All of this activity came off without a hitch. The backoffice process that will be written to submit reports to RS for processing will utilize the same API/DLL code that the Report Manager interface uses, so I assume my approach is a valid one. The backoffice program will need to know how many reports are running to be able to control the flow of requests. I am distressed because of this apparent failure of RS to keep track of an active snapshot build process. I am more distressed because there doesn't appear to be any way to figure out what happened. As noted previously, any time this situation occurs, the snapshot never completes. Any light which might be shed on our dilemma would be greatly appreciated.

Hi wbrian63, Based on your description, I understand you did a lots of efforts on troubleshooting the issue. And now, the issue is that you scheduled report snapshots, however, the snapshots won't be created correctly. You noticed the report disappeared from the RunningJobs table, however, the snapshot was not created(there is not no record about the report in the ExecutionLogStorage table). If we disabled the connection to Oracle, we will found the rsProcessingAborted error in the ExecutionLogStorage. If I have misunderstood, please don't hesitate to let me know. According to the analysis, the issue seems to be caused by there are lots of data in the report, and the report is stilling running(never end). However, there should have Report Server catalog database query timeout, then the entry for the report disappeare from the RunningJobs table. So, in order to troubleshoot the issue, please help to check if there is any error in the SQL Server Reporting Services error logs. Thanks, Jin ChenJin Chen - MSFT

Hi wbrian63, Based on your description, I understand you did a lots of efforts on troubleshooting the issue. And now, the issue is that you scheduled report snapshots, however, the snapshots won't be created correctly. You noticed the report disappeared from the RunningJobs table, however, the snapshot was not created(there is not no record about the report in the ExecutionLogStorage table). If we disabled the connection to Oracle, we will found the rsProcessingAborted error in the ExecutionLogStorage. If I have misunderstood, please don't hesitate to let me know. According to the analysis, the issue seems to be caused by there are lots of data in the report, and the report is stilling running(never end). However, there should have Report Server catalog database query timeout, then the entry for the report disappeare from the RunningJobs table. So, in order to troubleshoot the issue, please help to check if there is any error in the SQL Server Reporting Services error logs. Thanks, Jin ChenJin Chen - MSFT

Hi wbrian63, Based on your description, I understand you did a lots of efforts on troubleshooting the issue. And now, the issue is that you scheduled report snapshots, however, the snapshots won't be created correctly. You noticed the report disappeared from the RunningJobs table, however, the snapshot was not created(there is not no record about the report in the ExecutionLogStorage table). If we disabled the connection to Oracle, we will found the rsProcessingAborted error in the ExecutionLogStorage. If I have misunderstood, please don't hesitate to let me know. According to the analysis, the issue seems to be caused by there are lots of data in the report, and the report is stilling running(never end). However, there should have Report Server catalog database query timeout, then the entry for the report disappeare from the RunningJobs table. So, in order to troubleshoot the issue, please help to check if there is any error in the SQL Server Reporting Services error logs. Thanks, Jin ChenJin Chen - MSFT

Wow - I had completely forgotten that I'd posted this question. I stumbled on it via a Google query about the ExecutionLogStorage table.. Anyway - to update - it is possible that what I was seeing in the RunningJobs table was not the complete story. We subsequently discovered that RunningJobs is updated in a 60 second cycle interval from SQLAgent. Knowing (believing) that the processes that update this table will be stored procedures in SQLServer, we did some digging and discovered, quite surprisingly, that there is no Update proc, only Add and Delete. Further, there is no logic in the procs - the Insert proc accepts every column in RunningJobs as an input parameter, and inserts those values into RunningJobs - there's no "SELECT FROM" to pull the data from somewhere else in SQLSVR. This suggests to me that all of the information about running reports is held in memory somewhere... So, what appears to happen is every 60 seconds, if there is a change in the status of any of the records in the RunningJobs table, the entire table is erased. Then, 60 seconds later, the record is added back to the table. It's as if the agent can only handle one task per cycle - as in the following hypothetical example. Please understand I'm recalling this from December of 2010, so the accuracy may be less-than 100%, but I do remember seeing reports disappear and reappear in RunningJobs. Report X scheduled Agent Cycle - sees report - adds report to Running Jobs Report Y scheduled Agent Cycle - sees report Y - erases Running Jobs table (only one "action" apparently possible per cycle - Report Y is "new" data, but apparently, the entire table must be erased to add the new report back) Agent Cycle - adds Report X and Report Y back to table Report X completes Agent Cycle - Report X is gone - erase Running Jobs table Agent Cycle - add Report Y back to Running Jobs table Report Y completes Agent Cycle - erase Running Jobs table. I know that it is possible for a report to be scheduled, run and complete within the span of an Agent Cycle, because the report never appears in the RunningJobs table. I've tested this repeatedly and verified the behavior. In the previous example, what I "think" was happening was I was checking the RunningJobs table at "just the wrong time" when it was in the "erased" cycle. As our testing continued subsequent to my initial posting here, I learned to query RunningJobs, and then wait 60 seconds (or a bit more) and run the query again. If a report wasn't present during the first query, and was also not present during the 2nd query, then it can be assumed that the report has completed or failed. The backoffice program that will handle submitting reports to RS will manage its own queue, so the RunningJobs table won't be an integral part of our system, and its abberant (my opinion) behavior won't be an issue. I've managed to convince management that ANY report that is to be submitted to RSNative must go through the backoffice system, so we shouldn't have to worry about loading the server too heavily. Frankly, the implementation of this product leaves much to be desired. An enterprise-level application should allow for instantaneous querying of load, and there appears to be no such tool or SQL resource available that can tell us reliably what is really going on in RSNative. Of course, I could be missing an entire suite of applications/functionality that would allow me to do what I need (insofar as "watching" the system), and if so, I will gladly retract the previous statement... W. Brian Fogarty Sr. Programmer Analyst VALIC

It would appear that I wasn't correct in my assumption about the report actually not disappearing but instead being "disguised" by the way RunningJobs is updated by SQL Agent. Monday afternoon, I started a new snapshot running for a report I knew would take several hours to resolve. We have a nightly refresh of the data warehouse that serves our reports that starts at 8:00pm. I came back to work at 9:45pm to check on some other processes, expecting to see the snapshot I requested listed amongst the history choices for the report. No entry existed in the list. I checked the ExecutionLogStorage table for any evidence of the report's demise and found none. I checked the History table - same result. I checked the log files - there should have been some indication of the demise of the report around 8:00pm - NOTHING. To test further, I used my backoffice tester program to run the same snapshot on Tuesday AM. I then had our DBA kill the session from Oracle (Oracle 10g is the data source). The backoffice program immediately caught the error, and ExecutionLogStorage showed the expected record indicating the process was aborted. Looking in the log files also revealed the loss of connection to Oracle at the time the DBA killed the session. This method approximates the event that will occur when the data warehouse refreshes. Why would a report requested from Report Manager behave differently than one requested from a backoffice application using the CreateReportHistorySnapshot method???W. Brian Fogarty Sr. Programmer Analyst VALIC