Re: Timeout expired troubleshooting suggestions

From: Anthony Thomas (ALThomas_at_kc.rr.com)
Date: 01/19/05 

Date: Tue, 18 Jan 2005 22:45:45 -0600

I'd monitor different events inside the SQL Server Profiler. In addition to
the RPC Completed and Batch Completed, include the Error Events and the Lock
Escalation, Lock Timeout, and Deadlock Chain events. This will at least
give you a better idea of what was exectuing around the time the "timeouts"
were encountered.

Also, by the way, there are several "kinds" of timeouts: connection, query
(a.k.a., command), and lock waits. You will need to narrow these down. In
addition, if you are using connection pooling, you may be experiencing some
sort of pool master timeout. You should check into that as well.

Hope this helps.

Sincerely,

Anthony Thomas 

-- 
"Robert Richards via SQLMonster.com" <forum@SQLMonster.com> wrote in message
news:fc02b7bf3eb941bb90121fc9da176c76@SQLMonster.com...
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

Relevant Pages

  • Re: timeout expired on View
    ... I have a customer and their coding department experience this problem as well and I checked with them the Connection Timeout (in the client app's connection string and in SqlConnection' s Timeout property) and the problem persists. ... I believe that the reason of this problem is something else (anything, maybe a missing object at the SQL Server side or a deathlock etc.) than a direct timeout problem. ... > I have the same problem I'm running SSMS with MS SQL Server 2005. ...
    (microsoft.public.sqlserver.tools)
  • Re: Timeout - @@LOCK_TIMEOUT
    ... SQL Server internally needs to check if a transaction ... When this fails, the lock timeout ... This posting is provided "AS IS" with no warranties, and confers no rights. ...
    (microsoft.public.sqlserver.server)
  • Re: Data reader / Serverside cursor
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... When the first few rows are found, the agent tells the client that there is a resultset available to process. ... To "lock down" the rows, you're going to need a Transaction with a "repeatable read" option set. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Timeout - @@LOCK_TIMEOUT
    ... SQL Server internally needs to check ... >lock instead. ... >before the timeout. ... >> confers no rights. ...
    (microsoft.public.sqlserver.server)
  • Re: Monitoring database writes from remote script
    ... It would be easier for a client application/script to monitor itself. ... timeout setting and trap the error if the timeout happens. ... Unless there is a system stored procedure that can measure SQL Server ...
    (microsoft.public.windows.server.scripting)