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 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 amp  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    using Application  OnlineUsers     0    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

User · Answer

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

User · Answer

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

User · Answer

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       lt add name  ConnectionString  connectionString  Data Source ServerName User ID UserName Password Password Command Timeout 3600   providerName  System Data SqlClient    gt

User · Answer

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

User · Answer

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

User · Answer

TLDR     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 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    refactoring my code to use SqlBulkCopy increasing Timeout values  as stated in various answers or checking for underlying causes  may not be data related  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  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  Rebooting of the server s  - both application  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I had the same issue and resolved by adding  quot Connection Time quot  value in web config file  locate the connectionStrings and add Connection Timeout 3600 quot  here is the sample    lt connectionStrings gt       lt add name  quot MyConn quot  providerName  quot System Data SqlClient quot  connectionString  quot Data Source MySQLServer Initial Catalog MyDB User ID sa Password 123 Connection Timeout 3600 quot    gt     lt  connectionStrings gt

User · Answer

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 lt MyDbContext gt      gt                  UseSqlServer Configuration GetConnectionString  quot MyConnectionString quot    options   gt                         options CommandTimeout 180      3 minutes

User · Answer

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     There s a deadlock somewhere The database s statistics and or query plan cache are incorrect 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt StudentDbTable gt  StudentDbTables   get  set

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

Examples related to c#

Examples related to asp.net

Examples related to sql-server-2008-r2

Examples related to timeout

Examples related to sqlcommand