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?

Hi,

Try to run the cleanup tasks manually using the following code:

use ReportServer
TRUNCATE TABLE ExecutionLog

use ReportServerTempDB
TRUNCATE TABLE SnapshotData
TRUNCATE TABLE ChunkData
TRUNCATE TABLE SessionData

In addition, it may be caused by one report in this report server.

thanks,
Jerry

Hi

is it safe to truncate segment and chunksegmentmapping table in reportserver database?