Occasionally Getting SqlException: Timeout expired,如何解决?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (532)

我的服务器上运行着应用程序。这个应用程序的问题是,每天我会得到近10-20个错误:System.Data.SqlClient.SqlException Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

这是我的SP:

            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

这个SP有什么问题吗?为什么我只在这个SP中得到超时异常?这是堆栈追踪:

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& completedSynchronously)
提问于
用户回答回答于

您需要在服务器端对此进行调查,以了解执行超时的原因。注意,服务器没有超时,超时是由默认的30秒SqlCommand.CommandTimeout...

下面是一种可能的调查方法:

  1. 更改相关CommandTimeout到0(即无穷大)。
  2. 启用blocked process threshold,将其设置为30秒(前命令Timeout)
  3. 监测器阻塞进程报告事件
  4. 开始工作
  5. 查看Profiler是否生成任何报告事件。如果是的话,他们会找出原因。

如果超时是由阻塞引起的,这些操作将在每次获得超时时导致“阻塞进程报告”事件。如果阻塞是由活锁然后它会永远等待。

用户回答回答于

将这一行添加到连接字符串中:

Connect Timeout=200; pooling='true'; Max Pool Size=200

你可以myCom.CommandTimeout = 200

如果存在大量数据,也可以将超时秒从200秒增加到600秒。

也可以在web.config中编辑。

所属标签

可能回答问题的人

  • 天使的炫翼

    17 粉丝531 提问9 回答
  • 优惠活动秘书

    0 粉丝2 提问8 回答
  • 最爱开车啦

    8 粉丝503 提问7 回答
  • 富有想象力的人

    3 粉丝0 提问5 回答

扫码关注云+社区

领取腾讯云代金券