Timeout expired troubleshooting suggestions

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Robert Richards via SQLMonster.com (forum_at_SQLMonster.com)
Date: 01/19/05 

Date: Wed, 19 Jan 2005 00:24:36 GMT

We have a database that receives inserts from a .NET client. Periodically the client recieves timeout expired messages and all subsequent inserts fail, as though connectivity has been totally broken. We stop and restart the client services (not sql server) and connectivity is restored, but all the inserts that failed remain in a que. We have tested, and before we reset the services we can update and insert into the table using query analyzer, but the client cannot. The client is using the default command timeout of 30 seconds on the command object.

There is no sign of blocking (as per sp_blocker_pss80), dbcc traceon(3605,1204,-1) shows not signs of deadlocking, profiler shows the longest query durations under 2 seconds, performance monitor shows CPU, Memory, and Disk IO as doing very well.

NOTE: I mentioned above that once the client fails from the "timeout expired" that all subsequent inserts fail. There was one occasion when I archived [deleted] numerous records [from the table the client inserts into] to another table, and during this archive (I assume while a lock was on the table due to the delete) that the client timed out on several inserts (the archive took about 2 minutes, 120 seconds), but unlike all other instances, the client resumed inserting (connectivity was not broken) after timing out.

Any suggestions or ideas on how I might narrow down the troubleshooting effort on SQL Server, or is the application not handling the messages from SQL Server properly? 

-- 
Message posted via http://www.sqlmonster.com