May 3, 2007 at 11:30 am
This is driving me crazy. I've got a 2000 SP4 server with intermittant timeouts. For example, this morning at 8:24 am, about five processes timed out. I checked Perf Mon and everything looks great: disk i/o was low, processor was reasonable, buffer cache was 100, paging was minimal, page life expectancy, etc., etc.
You might say, "Well, what about the network?" Well, I don't think it's network and here's why: the Perf Mon counter "\SQLServer:Locks(_Total)\Lock Timeouts/sec" was much higher during this time period, about 10-14. So you say, "It's got to be a blocking/locking issue." Well, I've got a program that checks for anything with a significant waittime and then writes it to a table. There was zippo during that time frame!
Any ideas as to what could be causing these Lock Timeouts in Perf Mon? If you have problems on the client server, it won't cause lock timeouts in Perf Mon on the prod server, right?
Any things you can think of to look at would be much appreciated!
May 5, 2007 at 12:38 pm
Have you tried using SQL Server Profiler?
I think that SP:StmtCompleted might contain the detailed information showing what procedures are running, and what T-SQL statements each is executing. If you save the infomation to a table and query it, you might find out what is happening, and then you will know why a given process is timing out.
May 5, 2007 at 5:37 pm
Couple things: First do the processes run fine normally and then occassionally timeout?
Check that your connections or the server isn't set to have things timeout. By default queries run to completion, even if that is 10 days. You can set this lower if you like, so see if the query timeout in seconds is something other than 0.
May 5, 2007 at 10:52 pm
Yes. No long running or nasty queries during this time period in general.
May 7, 2007 at 9:51 am
Remote login and query timeouts have a run (and config) value of 1. Is that what you're talking about?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply