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?

Answer 1

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.