Reports timing out in web, run fine in designer.

I have implemented SQL reports a few week ago and everything has been
running fine until last weekend. What we are experiencing is that ALL
reports (even the small ones) are timing out, yet I can run them fine
in report designer.
We are running on a clustered web server install- I checked the IIS
timeouts on both servers (one is set to 120, the other is set to 300
seconds), but some of these reports shouldn't even be hitting the
timeout limit. Are there some settings or configuration changes that
I should look into to determine the cause of this performance slow-
down?
This is pretty urgent, so a quick reply would be very appreciated.
Thanks in advance.have your DBA check out the logs on the SQL server and run the SQL profiler
to see what is happening in the DBs.
"Just Another Reporter" <Crystal.Warren@.gmail.com> wrote in message
news:1184612034.766799.123730@.o11g2000prd.googlegroups.com...
>I have implemented SQL reports a few week ago and everything has been
> running fine until last weekend. What we are experiencing is that ALL
> reports (even the small ones) are timing out, yet I can run them fine
> in report designer.
> We are running on a clustered web server install- I checked the IIS
> timeouts on both servers (one is set to 120, the other is set to 300
> seconds), but some of these reports shouldn't even be hitting the
> timeout limit. Are there some settings or configuration changes that
> I should look into to determine the cause of this performance slow-
> down?
> This is pretty urgent, so a quick reply would be very appreciated.
> Thanks in advance.
>|||Also something to note- I am not able to deploy reports, I get the
same timeout error. I have manually uploaded using the reports
website, but then I am not able to delete any items (same error). I
have tested using my logon as well as the administrator logon.
I think this is a web server issue rather than a DB issue since I can
run the reports fine in designer. A look through the SQL logs didn't
provide any clues.
Here is an exerpt from the Reporting Services Log:
ERROR: Sql Error in CleanOrphanedSnapshots:
System.Data.SqlClient.SqlException: Timeout expired. The timeout
period elapsed prior to completion of the operation or the server is
not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32&
chunksCleaned)
w3wp!library!16d0!7/16/2007-15:28:49:: i INFO: Cleaned 0 batch
records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs
ERROR: Sql Error in CleanExpiredSessions:
System.Data.SqlClient.SqlException: Timeout expired. The timeout
period elapsed prior to completion of the operation or the server is
not responding.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at
Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSessions()
On Jul 16, 12:00 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> have your DBA check out the logs on the SQL server and run the SQL profiler
> to see what is happening in the DBs.
> "Just Another Reporter" <Crystal.War...@.gmail.com> wrote in messagenews:1184612034.766799.123730@.o11g2000prd.googlegroups.com...
>
> >I have implemented SQL reports a few week ago and everything has been
> > running fine until last weekend. What we are experiencing is that ALL
> > reports (even the small ones) are timing out, yet I can run them fine
> > in report designer.
> > We are running on a clustered web server install- I checked the IIS
> > timeouts on both servers (one is set to 120, the other is set to 300
> > seconds), but some of these reports shouldn't even be hitting the
> > timeout limit. Are there some settings or configuration changes that
> > I should look into to determine the cause of this performance slow-
> > down?
> > This is pretty urgent, so a quick reply would be very appreciated.
> > Thanks in advance.- Hide quoted text -
> - Show quoted text -|||I found out the problem. The SessionLock table in the
ReportServerTemp DB was locked up. I had to restart the sql service
and remove the rows in that table to fix the problem.
Now that we can run reports we are unable to execute the
subscriptions. Any old or new subscriptions just sit there and do not
update their status. I can see that the SQL jobs are executing with
no errors and there are no errors in the logs. Any suggestions?
On Jul 16, 1:54 pm, Just Another Reporter <Crystal.War...@.gmail.com>
wrote:
> Also something to note- I am not able to deploy reports, I get the
> same timeout error. I have manually uploaded using the reports
> website, but then I am not able to delete any items (same error). I
> have tested using my logon as well as the administrator logon.
> I think this is a web server issue rather than a DB issue since I can
> run the reports fine in designer. A look through the SQL logs didn't
> provide any clues.
> Here is an exerpt from the Reporting Services Log:
> ERROR: Sql Error in CleanOrphanedSnapshots:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout
> period elapsed prior to completion of the operation or the server is
> not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedS=na=ADpshots(Int32&
> chunksCleaned)
> w3wp!library!16d0!7/16/2007-15:28:49:: i INFO: Cleaned 0 batch
> records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ERROR: Sql Error in CleanExpiredSessions:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout
> period elapsed prior to completion of the operation or the server is
> not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSe=ss=ADions()
> On Jul 16, 12:00 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
>
> > have your DBA check out the logs on the SQL server and run the SQL prof=iler
> > to see what is happening in the DBs.
> > "Just Another Reporter" <Crystal.War...@.gmail.com> wrote in messagenews=:1184612034.766799.123730@.o11g2000prd.googlegroups.com...
> > >I have implemented SQL reports a few week ago and everything has been
> > > running fine until last weekend. What we are experiencing is that ALL
> > > reports (even the small ones) are timing out, yet I can run them fine
> > > in report designer.
> > > We are running on a clustered web server install- I checked the IIS
> > > timeouts on both servers (one is set to 120, the other is set to 300
> > > seconds), but some of these reports shouldn't even be hitting the
> > > timeout limit. Are there some settings or configuration changes that
> > > I should look into to determine the cause of this performance slow-
> > > down?
> > > This is pretty urgent, so a quick reply would be very appreciated.
> > > Thanks in advance.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||I figured this out. The sessionlock table was locked up. I couldn't
even return any rows from it. I restarted sql server service and
removed the rows. After this I was able to run reports again.
On Jul 16, 1:54 pm, Just Another Reporter <Crystal.War...@.gmail.com>
wrote:
> Also something to note- I am not able to deploy reports, I get the
> same timeout error. I have manually uploaded using the reports
> website, but then I am not able to delete any items (same error). I
> have tested using my logon as well as the administrator logon.
> I think this is a web server issue rather than a DB issue since I can
> run the reports fine in designer. A look through the SQL logs didn't
> provide any clues.
> Here is an exerpt from the Reporting Services Log:
> ERROR: Sql Error in CleanOrphanedSnapshots:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout
> period elapsed prior to completion of the operation or the server is
> not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32&
> chunksCleaned)
> w3wp!library!16d0!7/16/2007-15:28:49:: i INFO: Cleaned 0 batch
> records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
> chunks, 0 running jobs
> ERROR: Sql Error in CleanExpiredSessions:
> System.Data.SqlClient.SqlException: Timeout expired. The timeout
> period elapsed prior to completion of the operation or the server is
> not responding.
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> at
> Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSessions()
> On Jul 16, 12:00 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> > have your DBA check out the logs on the SQL server and run the SQL profiler
> > to see what is happening in the DBs.
> > "Just Another Reporter" <Crystal.War...@.gmail.com> wrote in messagenews:1184612034.766799.123730@.o11g2000prd.googlegroups.com...
> > >I have implemented SQL reports a few week ago and everything has been
> > > running fine until last weekend. What we are experiencing is that ALL
> > > reports (even the small ones) are timing out, yet I can run them fine
> > > inreportdesigner.
> > > We are running on a clustered web server install- I checked the IIS
> > > timeouts on both servers (one is set to 120, the other is set to 300
> > > seconds), but some of these reports shouldn't even be hitting the
> > > timeout limit. Are there some settings or configuration changes that
> > > I should look into to determine the cause of thisperformanceslow-
> > > down?
> > > This is pretty urgent, so a quick reply would be very appreciated.
> > > Thanks in advance.- Hide quoted text -
> > - Show quoted text -