Problem
My website, which is a download site for mobile downloads, has a large number of visitors (20000-60000 each day). My server is accessible from afar (windows server 2008-R2). I’ve previously received “Server is unavailable” errors, but now I’m getting a connection timeout error instead. I’m not sure what this means; why does it happen, and how can I repair it?
The full error is below:
EDIT AFTER RECEIVING ANSWERS: In Global.asax, my Application Start is as follows:
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 that has been invoked 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 strategies for attracting internet users:
As a result, I reset all OnlineUsers at Application Start for approach #2. In that table, there are over 482,751 records.
Asked by SilverLight
Solution #1
It appears that a query is taking longer than it should. You should be able to figure out what query that is based on your stack trace and code.
There are three reasons for this type of timeout:
It’s difficult to break a deadlock, but it’s simple to tell whether that’s the case. Sql Server Management Studio allows you to connect to your database. Right-click the server node in the left pane and select Activity Monitor. Take a peek at the processes that are now active. The most of the time, they will be idle or running. When an issue develops, the process state can be used to detect any halted processes. If you right-click on the process and pick information, you’ll see the process’s most recent query.
The database will utilize a sub-optimal query strategy as a result of the second issue. It’s possible to fix it by clearing the statistics:
exec sp_updatestats
If it doesn’t work, another option is to
dbcc freeproccache
You should not do this while your server is under a lot of stress since it will create a performance hit because all stored procedures and queries will be recompiled when they are initially run. However, since you say the problem only happens once in a while and the stack trace shows your application is starting up, I believe you’re executing a query that is only used once in a while. Forcing SQL Server not to reuse a previous query plan may be a better option. Details on how to do so can be found in this answer.
The third issue has already been discussed, but you can readily evaluate whether the query need optimization by manually running it, for example using SQL Server Management Studio. Even after resetting the statistics, you’ll probably need to tweak the query if it takes too long to complete. You should ask your issue in a new question if you need assistance with it.
Answered by Marnix van Valen
Solution #2
You should have something like this in your code where you run the stored procedure:
SqlCommand c = new SqlCommand(...)
//...
Add such a line of code:
c.CommandTimeout = 0;
This will take as long as the operation takes to complete.
Answered by Elastep
Solution #3
You might use the SQL Command’s CommandTimeout property to allow for a long-running SQL transaction.
You should also investigate the SQL Query that caused the timeout.
Answered by Kev Ritchie
Solution #4
Perhaps it will be useful to someone. I had the similar issue, and the cause was that the SqlConnection was opened and not closed in the method that I ran in a loop of 2500 repetitions. The connection pool was depleted. The problem was rectified by properly disposing of the waste.
Answered by eternity
Solution #5
While all of the previous replies addressed the issue, they did not cover all of the possible scenarios.
Microsoft acknowledged the problem in 2011 and corrected it for supported operating systems, so if you see a stack trace that looks like this:
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)
It’s possible that you’ll need to upgrade your.NET assemblies.
For more information, see KB 2605597.
Answered by matcheek
Post is based on https://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation