Timeout expired. The timeout period elapsed prior

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

Answer 1

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

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.

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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;
    }