SQL Reports

We have been working on a new project at work that requires the SQL 2005 Reporting Service. It is a little clunky to get installed, but eventually it works fairly well. My question is, what could cause it to bomb out the next morning after a period of inactivity? We are thinking that a service restart may be in order every morning or a job may need to be scheduled to run periodically to keep the service up. Any ideas of what could be causing this?
more ▼

asked May 03, 2011 at 05:51 AM in Default

xypher gravatar image

416 7 7 9

What do you see in the event log?
May 03, 2011 at 05:52 AM Raj More
whilst its a well used term, what do you mean when you say "bomb out"? Is it totally stopped and needs a reboot/restart? Is it just slow to respond? What are the errors?
May 03, 2011 at 08:12 AM Fatherjack ♦♦
Errors seem to point to the service not being available. A restart of the service works. The application server can't connect until that is done. I have thought of a possible timeout setting somewhere in IIS, but I am still in the information gathering stage of this issue... Thanks for all the suggestions!
May 03, 2011 at 08:26 AM xypher
The real cause appears to be due to inactivity. If someone pulls a report periodically, the issue does not come back. I was experiencing this issue with an application that was still in the testing phase, so it was not getting much traffic. That being said, this does not seem to be a big problem. Thanks for all the feedback.
May 13, 2011 at 10:16 AM xypher
Yeah, this is exactly what I was saying in my answer. The application pool is being restarted after a time of inactivity. :-)
May 14, 2011 at 12:33 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Are you experiencing the problem on the application server? I mean you receive timeouts on the application server?

If it is related to the daily periodics timeouts, then it could be related to the application pool shut downs which are done after some time of inactivity on SSRS 2005. Check the MS SQL Tips for How to Speed up MS SQL Server Reporting Services SSRS on First Run. There is tip how to keep the application pool to keep running without recycling.

SSRS2008 has similar problems witht the Application Domain Restarts. I have some info related to it here: [First report and report after specific time interval starts a long time on MS SQL 2008 Reporting Services][2] (It was ispired by the MS SQL Tip for SSRS 2005 :-))

[2]: http://www.pawlowski.cz/2011/01/report-report-specific-time-interval-starts-long-time-ms-sql-2008-reporting-services/
more ▼

answered May 03, 2011 at 11:42 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Good advice. Just to clarify, it is only the reporting that will timeout with the first activity of the day. The rest of the application works fine. The article on speeding up the first run of the day may be a good read. Thanks again.
May 03, 2011 at 01:05 PM xypher
Hi, the symptoms you describes fit the problem I've mentioned. After the inactivity the application pool is being shut down. Then in the morning with the first request the complete application pool is starting and you may experience the timeouts. Let us know whether it is the case or not.
May 03, 2011 at 09:49 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

You may wish to start with @Raj More suggestion..

Then you can look at the dbo.ExecutionLog it will give you an history of things like "TimeDataRetrieval", "TimeProcessing", "TimeRendering" and Status. You can join this table to dbo.Catalog to filter the exact report. [MS link][1]

Another place to look is within the LogFiles for the actual report instance. Microsoft SQL Server/MSSQL.3/Reporting Services/LogFiles

This in my location, yours maybe a little different.

Other basic quesitons would be, how many users did it affect? Is it still happening? At what time did it crash and was there anything else running at that time?

[1]: http://msdn.microsoft.com/en-us/library/ms159110%28v=SQL.90%29.aspx
more ▼

answered May 03, 2011 at 07:43 AM

sp_lock gravatar image

9.3k 25 28 31

Thanks for the information on the log location!
May 03, 2011 at 08:28 AM xypher
(comments are locked)
10|1200 characters needed characters left
My first line of investigation here would be network related - is there something dropping out of DNS or maybe a authentication issue somewhere? If the service is being reported as stopped is something causing it to stop overnight?
more ▼

answered May 03, 2011 at 08:33 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

We definitely have our network challenges :-). I'll be sure to entertain that in this investigation.
May 04, 2011 at 05:00 PM xypher
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 03, 2011 at 05:51 AM

Seen: 1133 times

Last Updated: May 03, 2011 at 05:51 AM