SQL Timeout through .NET code

Question

So we have a process that runs and it calls our DB to get some data. When we run the proc that gets called through SQL Server Management Studio, it takes anywhere from 6 minutes to 15 minutes to run, but does complete. When we run it through code, it always times out. My guess is that the code's expected timeout range gets exceeded, but the proc would actually still run if it could, since it does directly through the Management Studio interface.

Here's the exception details:

InnerException: System.Data.SqlClient.SqlException
       Message=Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=11
       LineNumber=0
       Number=-2
       Procedure=""
       Server=KSQCOREDBINT
       State=0

And here's the stack trace if it helps at all:

StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
            at System.Data.SqlClient.SqlDataReader.get_MetaData()
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
            at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
            at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
            at Chatham.Panda.DataAccessLayer.CoreDataContext.sp_calc_schedule_rates_retrieve(Nullable`1 se_sched_id) in c:\TeamCity\BuildAgent02\work\36e300184c20475\Chatham.Panda.DataAccessLayer\Core.designer.cs:line 2306
            at Chatham.Panda.EndOfDay.EodDataAccess.<>c__DisplayClass15.<SpCalcScheduleRatesRetrieve>b__14(CoreDataManager coreDataManager) in C:\svn\K2\trunk\panda\ScheduleBuilderService\Chatham.Panda.EndOfDay\EodDataAccess.cs:line 134
            at Chatham.Panda.EndOfDay.EodDataAccess.RunStoredProc[T](Func`2 storedProcDelegate, String storedProcName) in C:\svn\K2\trunk\panda\ScheduleBuilderService\Chatham.Panda.EndOfDay\EodDataAccess.cs:line 21

Anyway we can increase the allotted timeout range or something? We're continuously working on improving the stored proc, but that's up to the DB team. Just trying to see if I can do something on my end.

Thanks.

Increase the [SqlCommand's CommandTimeout](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx) or set it to 0(no limit). — Tim Schmelter, Nov 28 '11 at 17:14

score 4 · Answer 1 · answered Nov 28 '11 at 17:32

4

There are up to 3 timeouts to keep track of

connection time out - how long it takes to open a connection to the database
command time out - This is what applies you you now. This is how long a query can run before it ends.
page time out - I didn't see you say, but if this is an ASP.NET page, you can run into the page time out

answered Nov 28 '11 at 17:32

MatthewMartin

32,326
33
105
164

1

+1 And I'd like to add that you should in general keep your connection timeout periods **low**, and adjust your command timeout for the typical duration of your statement. – Yuck Nov 28 '11 at 17:35

score 4 · Accepted Answer · edited May 23 '17 at 12:14

SQL commands time out because the query you're using takes longer than that to execute. Execute it in Query Analyzer or Management Studio, with a representative amount of data in the database, and look at the execution plan to find out what's slow.

If something is taking a large percentage of the time and is described as a 'table scan' or 'clustered index scan', look at whether you can create an index that would turn that operation into a key lookup (an index seek or clustered index seek).

Troubleshooting Timeout SqlExceptions

you can set the CommandTimeout on a SqlCommand:

objCmd.CommandTimeout = 600

SqlConnection.ConnectionTimeout is read-only property

SQL Timeout through .NET code

2 Answers2