SQL Server Command Timeouts Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

Question

Hi there, we've had a ASP.NET web application deployed for a number of years now.... last week we migrated to a slightly slower server to save some money.

Now we're frequently getting command timeouts: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

This is understandable in that a slower server will take longer to produce results; take longer time and produce a timeout.

Is there any system-wide way to get SqlClient to set a longer timeout? We cannot change the code, as it's everywhere... we're using multiple data access technologies as well.

Maybe there's a default command timeout setting on a connection string? We just need to increase it by 30 seconds; we're happy to wait a bit longer for queries to return.

thanks

Answer 1

The connection string only allows you to specify the connection timeout. I don't know any other way to set application wide ADO.NET settings.

There are probably several easy fixes:

• Run SQL Profiler for half an hour or so, and create the indexes it suggests
• Purge old records from the database
• Add some extra RAM to the server
• Install a newer version of SQL Server

Answer 2

I'd look at that query's execution plan and try to speed it up. 30 seconds or more is a very long time for a sql query in a web application. you say:

we're happy to wait a bit longer for queries to return.

But I'd bet you'd be even more happy for it to run instantly. If you are extremely sure that you have all the proper joins, indexs, etc. and have updated statistics, etc, then the timeout may be the only way to go. However, usually a little optimization goes a long way in making a slow query much faster.

Answer 3

i solved the same problem with killing a running process (remote Excel Select query) that i found in activity monitor under processes

and note that in SSMS 2008 Activity Monitor is not under Management node and is in toolbar and have changed its interface a little too

and as a kill this process my alter table script with 2 add column was committed successfully and has nothing to do with execution timeout and is just locking issue