November 17, 2009 at 9:35 am
Hi,
My Report Server slows down in the afternoon and the following query is running on the database and CPU and physical IO keep increasing to the point only an IIS reset will fix the issue.
ReportServer.dbo.WriteChunkPortion;1
There are no errors in the SQL error log.
The message in the report services log is -
w3wp!dbcleanup!8!11/16/2009-09:56:18:: e 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.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32& chunksCleaned)
w3wp!library!8!11/16/2009-09:56:18:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams
I suspect the root of the problem is the fact that the chunkdata table in the reportserver database is 85Gb.
I have came across a suggestion that it is OK to truncate this table, but it was talking about the reportservertemp database rather than reportserver database.
any ideas?
November 17, 2009 at 5:50 pm
this might be of help:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 18, 2009 at 2:17 am
Hiya,
Thanks - I seen this post, but there are no other bottlenecks on the server at the time, even if I kick everone out of RS the issue still occurs.
I'm going to truncate the table today - I have a backup just incase....
Thanks again
M
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply