Solving a Timeout Error for SQL Query

Solving a timeout error for SQL query

SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand.CommandTimeout = 0;  // Set the Time out on the Command Object

How to solve TimeOut Expired Problem?

There's no "black voodoo magic" out there - either you can make your query go faster (return less data, improve the database design, find and apply indices that make your queries execute faster), or then increase the timeout you allow the query to run before a timeout is thrown.

Those are your two options - take your pick.

UPDATE: a little googling reveals:

Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
cmd.CommandTimeout = 120   ' number of seconds

Marc

How to Troubleshoot Intermittent SQL Timeout Errors

Run SQL trace of long running queries and deadlocks. This shows no
deadlocks at the times of the problems, and long running queries all
coincide with our timeout errors, but look to be a side effect, and
not the cause. Queries that are very basic that typically return
instantly end up taking 30, 60 or 120 seconds to run at times. This
happens for a few minutes then everything picks up and works fine
after that.

It looks like some queries/transaction lock your database till they are done. You have to find out which queries are blocking and rewrite them/run them at an other time to avoid blocking other processes. At this moment the waiting queries just timeout.

An extra point to dig into is the auto increment size of your transaction log and database. Set them on a fixed size instead of a percentage of the current files. If files are getting taller the time it takes to allocate enough space will eventually longer as your transaction timeout. And your db comes to a halt.

SQL Server TimeOut in C# After Adjusting timeout

Connection timeout and command timeout are two different things.

A connection timeout occurs when a connection cannot be retrieved from the connection pool within the allotted timeout period.

A command timeout occurs when a connection has been retrieved, but the query being executed against it doesn't return results within the allotted command timeout period. The default command timeout period in ADO.NET is 30 seconds.

If you've set the connection timeout to 300 seconds and you're still getting a timeout, it's likely a command timeout. As walkers01 said, set your command timeout to a suitable number of seconds. 300 seconds should be far more than sufficient; if your query executes in one minute in SSMS, a timeout of 90 seconds should suffice.

---

Related Topics