title: Occasionally Getting SqlException: Timeout expired
body: <p>I have application running on my server. The problem with this application is that daily I am getting nearly 10-20, <code>System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding</code> only one of my SP. Here is my SP,</p>
  
  <pre><code>            ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog] 
                              (@OS                NVARCHAR(50) 
                              ,@UniqueID          VARCHAR(500)
                              ,@Longitude         FLOAT 
                              ,@Latitude          FLOAT
                              ,@Culture           VARCHAR(10)
                              ,@Other             NVARCHAR(200)
                              ,@IPAddress         VARCHAR(50)
                              ,@NativeDeviceID    VARCHAR(50))
              AS 
              BEGIN 
  
                  DECLARE @OldUniqueID VARCHAR(500) = '-1';
                  SELECT @OldUniqueID = [UniqueID] FROM DeviceCatalog WHERE (@NativeDeviceID != '' AND [NativeDeviceID] = @NativeDeviceID);
  
                  BEGIN TRANSACTION [Tran1]
                      BEGIN TRY
                          IF EXISTS(SELECT 1 FROM DeviceCatalog WHERE [UniqueID] = @UniqueID) 
                          BEGIN 
                              UPDATE  DeviceCatalog 
                                 SET  [OS] = @OS
                                     ,[Location] = geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100 ), @Longitude) + ' ' + CONVERT(VARCHAR(100), @Latitude) + ')', 4326)
                                     ,[Culture] = @Culture
                                     ,[Other] = @Other
                                     ,[Lastmodifieddate] = Getdate()
                                     ,[IPAddress] = @IPAddress
                              WHERE   [UniqueID] = @UniqueID;
                          END
                          ELSE 
                          BEGIN
                              INSERT INTO DeviceCatalog
                                          ([OS]
                                          ,[UniqueID]
                                          ,[Location] 
                                          ,[Culture] 
                                          ,[Other]
                                          ,[IPAddress]
                                          ,[NativeDeviceID])
                                  VALUES  (@OS
                                          ,@UniqueID
                                          ,geography::STGeomFromText('POINT(' + CONVERT(VARCHAR(100) ,@Longitude) + ' ' + CONVERT(VARCHAR(100), @Latitude) + ')', 4326) 
                                          ,@Culture
                                          ,@Other
                                          ,@IPAddress
                                          ,@NativeDeviceID);
                                  IF(@OldUniqueID != '-1' AND @OldUniqueID != @UniqueID)
                                  BEGIN
                                      EXEC DeleteOldDevice @OldUniqueID, @UniqueID;
                                  END
                          END
                          COMMIT TRANSACTION [Tran1];
                      END TRY
                      BEGIN CATCH
                          ROLLBACK TRANSACTION [Tran1];
                          DECLARE @ErrorNumber nchar(5), @ErrorMessage nvarchar(2048);
                          SELECT
                              @ErrorNumber = RIGHT('00000' + ERROR_NUMBER(), 5),
                              @ErrorMessage = @ErrorNumber + ' ' + ERROR_MESSAGE();
                          RAISERROR (@ErrorMessage, 16, 1);
                      END CATCH
              END
  </code></pre>
  
  <p>Is there is any issue with this SP? Why I am getting Timeout exception only in this SP? Here is the Stack Trace,</p>
  
  <pre><code>System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
     at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
     at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
     at App.Classes.DBLayer.Execute(SqlCommand command, Boolean executeNonQuery)
     at App.Helpers.SQLHelper.GetResult(List`1 parameters, Boolean storedProcedure, String commandText, ResultType type)
     at App.Helpers.SQLHelper.ExecuteNonQuery(List`1 parameters, Boolean storedProcedure, String commandText)
     at App.Services.DeviceCatalogService.InsertOrUpdateDeviceCatalog(DeviceCatalog deviceCataLog)
     at WebApplication1.Handlers.RegisterDevice.ProcessRequest(HttpContext context)
     at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
     at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean&amp; completedSynchronously)
  </code></pre>