SQL timeout expired on fast query

Question

I am doing some heavy lifting in an SQL database, from C#.

I keep getting the following timeout exception:

 System.Data.SqlClient.SqlException:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

I run a large number of queries (100k+), and it isn't any one specific query that causes the exception - some times I manage to finish all of them with no exception.

Some of the exceptions come from queries run as part of a larger transaction, some of them come from queries run on their own.

When I run the same query directly in SQL Server Management Studio, they take less than a second to run.

I use the samme SQLConnection object for all the queries, but new SQLCommand object for each.

When I try using the SQL Server profiler, it just says that the batch/RPC has been completed.

Why am I getting this exception at random?

Sachin Shanbhag · Accepted Answer · 2011-03-16 12:01:20Z

4

You can try to increase timeout interval in your connection string and check once.

Another reason could be that there are some locks in DB which are blocking your query to execute. Check using the sp_lock, sp_who, sp_who2 to see if there are any locks in table.

answered Mar 16, 2011 at 12:01

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Máster Over a year ago

Use sp_who4 to show the actual query: saji-sqlsolutions.blogspot.com/2013/04/spwho4.html

AdaTheDev · Accepted Answer · 2011-03-16 12:02:10Z

2

It could be due to blocking - perhaps the times when it does time out, it's being blocked by another process that it's waiting for to release locks. This would explain why it appears seemingly at random as it depends on what's going on at the time.

answered Mar 16, 2011 at 12:02

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

1 Comment

Joda Over a year ago

I managed to solve the problem by putting everything in a transaction, in stead of only some of it - now I have read-locks on all the tables I need from the beginning, and thus avoid blocking problems.

Peter Schofield · Accepted Answer · 2011-03-16 12:10:52Z

0

I'd agree with the other advice to check the blocking/locks, and see what is occurring during usage. I would also certainly do a healthcheck on the server - check the SQL and NT logs, ensure that SQL has enough memory, and check the wait stats with a view to trying to minimise them.

answered Mar 16, 2011 at 12:10

Peter Schofield

9937 silver badges14 bronze badges

Comments

bjorsig · Accepted Answer · 2011-03-16 12:15:21Z

0

The most likely explanation are locks. Analyse that specially if you are doing updates, inserts and/or deletes. Re-arranging the queries could do the trick.

answered Mar 16, 2011 at 12:15

bjorsig

1,1172 gold badges7 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL timeout expired on fast query

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related