SQL Server Timeout

Question

SQL Server Timeout

Fart Monster

Newbie

Points: 8
More actions
August 14, 2007 at 9:38 am

#62845

Our application is showing timeout error at least a few times a week. Here's what shown in the event log. Event Type: Error
Event Source: [Application Name]
Event Category: None
Event ID: 1280
Date:  6/19/2007
Time:  5:44:27 AM
User:  N/A
Computer: [App Server Name]
Description:
Unexpected error: [Microsoft][ODBC SQL Server Driver]Timeout expired
Recently it's occuring consistently around 11am. We have been running SQL Profiler and Perf Mon for weeks to figure out what's hogging resources. We can see serious file read & write during this time, our application vendor insists that it's not their queries. To cover all basis, we have made sure no jobs are running around this time. We have turned off Auto Statistis Update, and eliminated one backup job during the day. All we have is the hourly transaction log backup. Our nightly maintenance plan includes build indexes, and manual statistics updates (sampling 50%). This is a three-tier client-server app running in a cluster env., the error is only logged to the app server and nothing on the SQL server. Appreciate any ideas on how to diagnose/fix this issue.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29451 More actions · Answer 1

SQL Profiler might be your next option - if you know what is actually happening on the SQL Server at the time the timeout occurs, you should be able to work out why the timeout is occurring.

It is so convenient that the issue is happening around 11am - I would start Profiler up well before this and stop it a little why afterwards. This may sound a little vague but it is possible that the timeout is happening because a lock is being held for too long.

Configure profile to capture statement start and completion as well as stored procs starting and completing.

You may end up with a very large trace depending on how busy the server is. I would save the result to a table and then start to analyse what is in it. Maybe a single long running query.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

I wouldn't be surprised if it was a Java or C# batch job running at that time SQL Scheduled Jobs aren't the only show in town

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Fart Monster Newbie Points: 8 More actions · Answer 3

That's what we suspect too. I've been using pslist to see if I catch the culprit and no luck so far. No external process that I can see.

We do start our traces early and let it run pretty much throughout the day. We've been quite cautious about turning on too many parameters for fear of degrading server performance. Speaking of deadlocks, we do notice high volume of deadlocks around the timeout. I should turn on the trace flag for that. Thanks for reminding me about that.