Archive for the ‘Troubleshooting Event 31552’ Category

Event 31552 – Exception ‘SqlException’: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

March 29, 2010 Leave a comment

Kevin Holman posted some great information on this on the technet forums:

Kevin Holman
This is caused by your backup process – or your own in-house SQL maintenance taking all the I/O on the warehouse, and impacting the performance of the built in housekeeping of SCOM.

This is all on the warehouse database.

You should first check your backup – and set this to only backup once per week, instead of nightly, if that is the case. You should also see where it is backing up to… and make sure it isnt backing up directly to tape (which can often take too long) or backing up to the same disk spindles that the DB resides on (this will cause more I/O contention.

Next – are you doing any in house maintenance? Such as running anything like a DBCC checkDB, Update statistics, Reindex, etc? If so – you should really not do this against the warehouse. This is all built into the product – and often SQL DBA’s just apply their “standard package” of maintenance which actually causes problems.

Look in your SQL job history – find which jobs are taking the longest – and find out why – or if you really should be running these at all.

Next – consider tuning all the stuff you are throwing in the warehouse. Look at your most common event, perf, and state changes… and fix/tune/disable the unecessary junk to reduce the load and storage impact…. thereby reducing maintenance operations.

Lastly – improve the I/O by making sure you have enough spindles in a RAID 10 database, with distinct spindles for DB and logs, to provide the necessary I/O for maintenance operations on this large database.