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

1,007,569

Solution 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.

Solution 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.

Solution 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.

Solution 4

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.

Solution 5

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>
Share:
1,007,569
SilverLight
Author by

SilverLight

WEB DEVELOPER &amp; C# PROGRAMMER ASP.NET C# JQUERY JAVASCRIPT MICROSOFT AJAX MICROSOFT SQL SERVER VISUAL STUDIO

Updated on December 07, 2021

Comments

  • SilverLight
    SilverLight over 2 years

    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 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) +11686928 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.