Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
danielc
Starting Member
49 Posts |
 Posted - 2010-04-28 : 19:40:59
|
| Hello all,Several of our users sometimes generate large reports and become impatient and abort the report before completion. What I have noticed when they do abort the report a blocking chain will occur. The blocking chain becomes so intensive that the report server will restart itself. Has anyone encountered such behavior and if so how was this remedied?Thanks,D |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-04-28 : 19:45:19
|
| The blocking would have happened even if the report didn't get cancelled. When the report gets cancelled, SQL Server isn't aware of it so the query continues to run. It's the report's query that is actually the problem. To remedy it, run reports on a reporting system that gets replicated to from production. Check for missing indexes, update statistics on a scheduled basis, and rebuild indexes on a scheduled basis. Also, make sure the query is as efficient as possible.What do you mean that the report server restarts? Does it crash? By report server, are you referring to the web server in Reporting Services?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
danielc
Starting Member
49 Posts |
Posted - 2010-05-03 : 19:41:00
|
| Thanks for your quick response. What restarted, was sql server itself not the server. It seems as though it could not handle the load and sql server restarted. The server did not though. I looked at the create date of the tempdb and it was the date when sql server restarted. I looked through the ReportServerService and ReportServer logs and I see the following:ReportingServicesService!dbcleanup!1c!4/26/2010-10:42:34:: 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)Thank you for the suggestions, I will look into the missing indexes. What are your recommendations to updating statistics and rebuilding indexes frequency? Some folks say every day and others prefer once a week. Our reporting system is extremely busy.D |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-05-03 : 19:49:09
|
| Well it depends on your system. There isn't just one answer. We do ours daily, but our system receives tons of data modifications each day. If the SQL Server service crashed, then check the SQL Server Error Log and Event Log for more information. Even under tremendous load, the service should not restart. What build of SQL Server 2005 are you using? Run SELECT @@VERSION to check. It should be at a minimum build of 4262.What isolation level is being used? The recommendation is to use READ_COMMITTED_SNAPSHOT, however that isn't the default.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Blocking...