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

Question

I have many users on my web site (20000-60000 per day), which is a download site for mobile files. I have remote access to my server (windows server 2008-R2).
I've received "Server is unavailable" errors before, but I am now seeing a connection timeout error.
I'm not familiar with this - why does it occur and how can I fix it?

The full error is below:

Server Error in '/' Application. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: 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.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +404
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +412
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1363
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +6387741
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +6389442
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +538
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +689
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +327
NovinMedia.Data.DbObject.RunProcedure(String storedProcName, IDataParameter[] parameters, Int32& rowsAffected) +209
DataLayer.OnlineUsers.Update_SessionEnd_And_Online(Object Session_End, Boolean Online) +440
NiceFileExplorer.Global.Application_Start(Object sender, EventArgs e) +163

[HttpException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.]
System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +4052053
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +191
System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +352
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +407
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +375

[HttpException (0x80004005): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) [phone] System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +141 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +4863749

---

EDIT AFTER ANSWERS:
my Application_Start in Global.asax is like below:

protected void Application_Start(object sender, EventArgs e)
{
    Application["OnlineUsers"] = 0;

    OnlineUsers.Update_SessionEnd_And_Online(
        DateTime.Now,
        false);

    AddTask("DoStuff", 10);
}

The stored procedure being called is:

ALTER Procedure [dbo].[sp_OnlineUsers_Update_SessionEnd_And_Online]
    @Session_End datetime,
    @Online bit
As
Begin
    Update OnlineUsers
    SET
        [Session_End] = @Session_End,
        [Online] = @Online

End

I have two methods for getting online users:

1. using Application["OnlineUsers"] = 0;
2. the other one using database

So, for method #2 I reset all OnlineUsers at Application_Start. There are over 482,751 records in that table.

Acierate

As it says here Default is 15 seconds – Rampant

Better to make root cause analysis, There are various reasons to cause such problem. Most basic is complex structure of query. I faced same issue when I fetch Images which stored as Hex values in table. – Cherokee

Other than the causes above, I'll add one more: Lock timeout: learn.microsoft.com/en-us/sql/t-sql/statements/… If this thread waiting for the lock too long, it will timeout based on above document. – Helminthic

Restarting the SQL Server in Services fixed the issue for me. – Fatuity

Note: One other possible cause of this error is if you're using a transaction in your C#/VB.NET code, then calling other functions/subs that access the database from within the transaction. The way to resolve this is by passing the db to the nested functions/subs so they are treated as part of the same transaction. (Note: Ideally I would recommend not doing your transactions in your application code; do them within the SQL code instead.) – Kall

Answer 1

Looks like you have a query that is taking longer than it should. From your stack trace and your code you should be able to determine exactly what query that is.

This type of timeout can have three causes;

1. There's a deadlock somewhere
2. The database's statistics and/or query plan cache are incorrect
3. The query is too complex and needs to be tuned

A deadlock can be difficult to fix, but it's easy to determine whether that is the case. Connect to your database with Sql Server Management Studio. In the left pane right-click on the server node and select Activity Monitor. Take a look at the running processes. Normally most will be idle or running. When the problem occurs you can identify any blocked process by the process state. If you right-click on the process and select details it'll show you the last query executed by the process.

The second issue will cause the database to use a sub-optimal query plan. It can be resolved by clearing the statistics:

exec sp_updatestats

If that doesn't work you could also try

dbcc freeproccache

You should not do this when your server is under heavy load because it will temporarily incur a big performace hit as all stored procs and queries are recompiled when first executed. However, since you state the issue occurs sometimes, and the stack trace indicates your application is starting up, I think you're running a query that is only run on occasionally. You may be better off by forcing SQL Server not to reuse a previous query plan. See this answer for details on how to do that.

I've already touched on the third issue, but you can easily determine whether the query needs tuning by executing the query manually, for example using Sql Server Management Studio. If the query takes too long to complete, even after resetting the statistics you'll probably need to tune it. For help with that, you should post the exact query in a new question.

Ephram

I was having this same error but in a query that 'just' took 8secs... and your tip about the exec sp_updatestats solved my problem. Many thanks! – Semiology

Solving a problem like this is almost never a matter of tuning timeouts or the connection pool size. You'll need to dive in and figure out the root cause. If you need help resolving that root cause you can post your own question. – Ephram

This is certainly not a deadlock. It may be caused by excessive blocking, but deadlocks are resolved in a second, and they generate different errors. This may be excessive blocking, but not a deadlock. – Antitrades

are we sure this is a Command Timeout and not a Connection Timeout? "System.Data.SqlClient.SqlConnection.OnError" to me indicates a Connection issue. – Swallowtail

If you want to update the stats for just a specific stored proc, you can always modify it and then execute/save it again. I'm sure there is a neater way to do this, but it works – Spurgeon

This "exec sp_updatestats" thing fixed my issue too. Does anyone know how this situation gets created in the first place? Can it be avoided? Or is this just a unavoidable maintenance task that needs to be taken every so often? – Anachronistic

@MarnixvanValen Should I run exec sp_updatestats command on my production server? – Reductive

@PrashantPimpale It depends If you have a serious issue in production where incorrect statistics are resulting in bad execution plans then, yes, this could be a solution. Barring exceptional issues (like hardware failure), updating statistics will not break your database. It may cause slower queries for a bit. In the end, it's your call though. – Ephram

I just had the same error on a query that only runs for 5 seconds in management studio, and exec sp_updatestats fixed it. – Uncover

sp_updatestats: used it for the first time and it cleaned up all clogged arteries of my application. Almost all procs are running on much improved performance. Many thanks Marnix! – Tailrace

exec sp_updatestats did not fix the issue but after running "dbcc freeproccache" the issue is resolved. Thank you! – Spectrometer

Answer 2

In your code where you run the stored procedure you should have something like this:

SqlCommand c = new SqlCommand(...)
//...

Add such a line of code:

c.CommandTimeout = 0;

This will wait as much time as needed for the operation to complete.

Boulanger

You should also be aware that the 0 value is not recommended: A value of 0 indications no limit, and should be avoided in a CommandTimeout because an attempt to execute a command will wait indefinitely. It is better to learn how much time the command takes, and increase the Timeout value if needed. – Deduct

I agree with Otiel and downvoted your answer: When setting the commandTimeout to 0 you don't give the webserver a chance to recover from a database server that is not respondig. Secondly when you hitting the default timeout you should consider looking at the cause. In most cases it's better to fix the query than raising the timeout time. – Topee

I wouldn't fall into the trap of not recommending it. It's been very useful for me and my daily scheduled tasks: Having an infinite timeout does not stop a process completing and returning an error if something goes wrong. Simply put, it just aloows you to allow a query to finish whenever it needs to, without giving yourself problems later on, because you hadn't allocated enough time for the process to finish. You can also avoid locking up your program by multi-threading. – Onitaonlooker

Yes for large data transfers not setting this does not make sense. If you are transferring millions of rows then what Otiel and BlackHawkDesign are saying does not make sense. – Amianthus

In 20 years of data-centric development, I've never needed to do this. There's almost always a reasonably simple solution that gives better performance and doesn't create an opportunity for a single process to grind away at a database all day long. The vast majority of db performance problems can be tuned to where they perform several order of magnitudes faster. Ie, that process you're waiting 3 hours to complete, could probably be tuned to 3 minutes or even 3 seconds. – Introit

Sometimes you just have to wait for a query for an unknown period of time. And it's not related to bad queries at all.. Like imagine downloading a huge file from database with a poor connection. One very important thing to understand is that setting the timeout to zero will not stop you from getting all other type of exceptions. If the query fails, connection drops or anything else happens you'll get the exception. The only thing it does - eliminates the time factor. – Boulanger

It was a solution for me. Thanks a lot! Because my query naturally executes very long time, it collects huge amount of statistic, and executes about 15 min. – Epilate

Answer 3

You could set the CommandTimeout property of the SQL Command to allow for the long running SQL transaction.

You might also need to look at the SQL Query that is causing the timeout.

Ryan

hi, "or you need to look at the SQL Query that is causing the timeout" -> in sql server 2008 where should i check for that timeout? – Acierate

You may need to test the Stored Procedure that is called from DataLayer.OnlineUsers.Update_SessionEnd_And_Online as the Stack Trace seems to point towards it. Take a copy of the live database into test and run the Stored Procedure passing in the required parameters, if it takes any more than 30 seconds to complete, that is why you are getting a time out. I'm assuming you have access to SQL Server Management Studio. – Ryan

yes, i have access to sql server 2008. i should try your way. – Acierate

If you find the stored procedure that is causing the problem, you can run the query contained in the stored procedure through the Database Tuning Adviser which will suggest if you need any indexes etc. Link here msdn.microsoft.com/en-us/library/ms174202.aspx – Ryan

Answer 4

I had the same issue and resolved by adding "Connection Time" value in web.config file. locate the connectionStrings and add Connection Timeout=3600"

here is the sample

  <connectionStrings>
    <add name="MyConn" providerName="System.Data.SqlClient" connectionString="Data Source=MySQLServer;Initial Catalog=MyDB;User ID=sa;Password=123;Connection Timeout=3600" />
  </connectionStrings>

Warehouse

Is it seconds? milliseconds? – Inequitable

it is in seconds. refer [link](learn.microsoft.com/en-us/dotnet/api/… ) – Warehouse

if that is second, it should not one hour for one query / transaction , maybe the ideal is 15 minutes. so 60 * 15 = 900 depends on situation. – Privett

Answer 5

Maybe it will be useful for somebody. I faced with the same problem and in my case the reason was the SqlConnection was opened and not disposed in the method that I called in loop with about 2500 iterations. Connection pool was exhausted. Proper disposing solved the problem.

Marte

this! exactly this. My problem was that I was firing off methods on a different thread without waiting for them (because the user does not need a result of that method, background script). Without disposing (making use of using blocks) I've got this timeout issue. This seemed to solve it. – Aberdeen

did you get the time out error with connection pool max reached or Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Because, if you receive the max pool reached, it makes sense to look into leaked connection. but, i am getting server not responding error. – Noach

Answer 6

While all the earlier responses address the issue they did not cover all cases.

Microsoft has acknowledged the issue and fixed it in 2011 for supported operating systems, so if you get the stack trace like:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

you may need to update your .NET assemblies.

This issue occurs because of an error in the connection-retry algorithm for mirrored databases.

When the retry-algorithm is used, the data provider waits for the first read (SniReadSync) call to finish. The call is sent to the back-end computer that is running SQL Server, and the waiting time is calculated by multiplying the connection time-out value by 0.08. However, the data provider incorrectly sets a connection to a doomed state if a response is slow and if the first SniReadSync call is not completed before the waiting time expires.

See KB 2605597 for details

https://support.microsoft.com/kb/2605597

Leisaleiser

Answer 7

You have to set CommandTimeout attribute. You can set the CommandTimeout attribute in DbContext child class.

public partial class StudentDatabaseEntities : DbContext
{
    public StudentDatabaseEntities()
        : base("name=StudentDatabaseEntities")
    {
        this.Database.CommandTimeout = 180;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<StudentDbTable> StudentDbTables { get; set; }
}

Shanley

Answer 8

I faced same problem worked on it around 3 days. I noticed as our number of records are not much our senior developer keeps 2 images and Fingerprint in database. When I try to fetch this hex values it taking long time, I calculate average time to execute my procedure its around 38 seconds. The default commandtimeout is 30 seconds so its less than average time required to run my stored procedure. I set my commandtimeout like below

cmd.CommandTimeout = 50

and its working fine but sometimes if your query takes more than 50 seconds it will prompt same error.

Cherokee

Answer 9

If you are using ASP.NET Core with the Startup.cs convention, you can access and set the query command timeout option like this:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContextPool<MyDbContext>(_ =>
    {
        _.UseSqlServer(Configuration.GetConnectionString("MyConnectionString"), options => 
        {
            options.CommandTimeout(180); // 3 minutes
        });
    });
}

Conduit

Answer 10

I encountered this error recently and after some brief investigation, found the cause to be that we were running out of space on the disk holding the database (less than 1GB).

As soon as I moved out the database files (.mdf and .ldf) to another disk on the same server (with lots more space), the same page (running the query) that had timed-out loaded within three seconds.

One other thing to investigate, while trying to resolve this error, is the size of the database log files. Your log files just might need to be shrunk.

Archilochus

Answer 11

Default timeout is 15 seconds, to change that, 0 is unlimited, any other number is the number of seconds.

In Code

using (SqlCommand sqlCmd = new SqlCommand(sqlQueryString, sqlConnection))
   {
      sqlCmd.CommandTimeout = 0; // 0 = give it as much time as it needs to complete
      ...
    }

In Your Web.Config, "Command Timeout=0;" do not time out, or as below 1 hour (3600 seconds)

  <add name="ConnectionString" connectionString="Data Source=ServerName;User ID=UserName;Password=Password;Command Timeout=3600;" providerName="System.Data.SqlClient" />

Fia

Those are two different timeouts. Your first suggestion addresses the question. Your second would only work if the connection provider supported it, which SqlClient doesn't. At any rate, a timeout of 0 is never a good idea in production. 30 seconds is the usual default. – Erivan

Just to clarify is "Connection Timeout=3600" not "Command Timeout =3600" in the web.config (The other one doesnt exits) – Tropopause

Answer 12

I have issue with large calculation in sp_foo that take large time so i fixed
with this little bit code

public partial class FooEntities : DbContext
{
   public FooEntities()
         : base("name=FooEntities")
    {
        this.Configuration.LazyLoadingEnabled = false;

        // Get the ObjectContext related to this DbContext
        var objectContext = (this as IObjectContextAdapter).ObjectContext;

        // Sets the command timeout for all the commands
        objectContext.CommandTimeout = 380;
    }

Lomalomas

Answer 13

TLDR:

1. Rebooting both application and DB servers is the quickest fix where data volume, network settings and code haven't changed. We always do so as a rule
2. May be indicator of failing hard-drive that needs replacement - check system notifications

---

I have often encountered this error for various reasons and have had various solutions, including:

1. refactoring my code to use SqlBulkCopy
2. increasing Timeout values, as stated in various answers or checking for underlying causes (may not be data related)
3. Connection Timeout (Default 15s) - How long it takes to wait for a connection to be established with the SQL server before terminating - TCP/PORT related - can go through a troubleshooting checklist (very handy MSDN article)
4. Command Timeout (Default 30s) - How long it takes to wait for the execution of a query - Query execution/network traffic related - also has a troubleshooting process (another very handy MSDN article)
5. Rebooting of the server(s) - both application & DB Server (if separate) - where code and data haven't changed, environment must have changed - First thing you must do. Typically caused by patches (operating system, .Net Framework or SQL Server patches or updates). Particularly if timeout exception appears as below (even if we do not use Azure):
   • System.Data.Entity.Core.EntityException: An exception has been raised that is likely due to a transient failure. If you are connecting to a SQL Azure database consider using SqlAzureExecutionStrategy. ---> System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) ---> System.ComponentModel.Win32Exception: The semaphore timeout period has expired

Edgerton

How-to refactoring to use SqlBulkCopy ? – Glutamate

Answer 14

I had this problem once and in my case was an uncommited transaction in SQL. After I commited, the problem went away.

Varsity

Answer 15

@SilverLight.. This is clearly an issue with a Database object. It can be a badly written query, or missing indexes. But as of now I won't suggest you to increase the timeout without investigating the issue with your Database objects

NovinMedia.Data.DbObject.RunProcedure(String storedProcName, IDataParameter[] parameters, Int32& rowsAffected) +209

Put a breakpoint on this line of code to findout the procedure name and then optimise the procedure by looking at its execution plan.

I cannot help you more till the time you post details about the stored procedure.

Avaunt

Stored Procedure does not do any fancy stuff. However it seems like the OnlineUsers table is being locked while the procedure is being executed. Try SQL profiler to see what is happening at Application_Start – Avaunt

Answer 16

In my case I used EntityFrameworkCore.
Since the input I wanted to be processed exceeded the query limit this error came to me.
The solution for my case was to split the data processing into chunks and by this approach I was able to fit within the limits.
Yes, it takes time, but at least it processes all of the input records.

Flotilla

Answer 17

try

EXEC SP_CONFIGURE 'remote query timeout', 1800
reconfigure
EXEC sp_configure

EXEC SP_CONFIGURE 'show advanced options', 1
reconfigure
EXEC sp_configure

EXEC SP_CONFIGURE 'remote query timeout', 1800
reconfigure
EXEC sp_configure

then rebuild your index

Demure

Can you explain your solution? – Endurable

How rebuild your index ? – Glutamate

Answer 18

Also make sure you just don't have a pending transaction. :)

I was doing some tests around and began a transaction to be safe but never closed it. I wish the error would have been more explicit but oh well!

Incumber

Answer 19

We've had hard times on Timeout expired/max pool reached Sqlexception. As a workarround and to prevent restarting the server or the service we modify the MAX SERVER MEMORY variable in SQL Server (either through SQL Managment Studio or T-SQL):

DECLARE @maxMem INT = 3000 --Max. memory for SQL Server instance in MB
EXEC sp_configure 'show advanced options', 1
RECONFIGURE

This temporarily fixes the issue until it happens again. In our case we suspect that it has to do with connection leaks at app level.

Kinnikinnick

MAX SERVER MEMORY ?? what is @maxMem ? – Glutamate

Answer 20

We recently upgraded to the NuGet version of SqlClient (Microsoft.Data.SqlClient) which contains a bug. This bug was introduced during the lifetime of the 1.x cycle and has already been fixed. The fix will be available in the 2.0.0 release which is not available at the time of this writing. A preview is available.

You can inspect the details here: https://github.com/dotnet/SqlClient/issues/262

Clunk

Answer 21

I had this problem and it went away when I moved from ADO.Net to Dapper for my queries.

Smithsonite

Answer 22

Timeout expired because the sql query is taking more time than you set in sqlCommand.CommandTimeout property.

Obviously you can increase CommandTimeout to solve this issue but before doing that you must optimize your query by adding index. If you run your query in Sql server management studio including actual execution plan then Sql server management studio will suggest you proper index. Most of the case you will get rid of timeout issue if you can optimize your query.

Paquito

Answer 23

Also you need to check if individual record is not getting updated in the logic because with update trigger in the place causes time out error too.

So, the solution is to make sure you perform bulk update after the loop/cursor instead of one record at a time in the loop.

Khalid

Answer 24

As others have said, the problem might be related with a pending transaction. In my case I had to send the DbTransaction variable as a parameter in the ExecuteScalar method for the procedure to be executed properly.

Before:

ExecuteScalar(command)

After:

ExecuteScalar(command, transaction)

Mattox

Answer 25

i got same error on my multithreading program that has over 2000 user connected simultansely. the problem happened when i run an SELECT command with more than 5000 rows. this command blocked by insert command. by changeing SELECT * to SELECT Top(n) * that n<5000 it fixes

Brittaniebrittany

Answer 26

Did you perhaps forget to await an async statement? You would get a similar message if your task ends up taking longer then when your thread was aborted.

Gober