0

I have a table log_table, and I have two stored procedures: insert_sp for inserting log entries into the table, and update_sp for performing update operations.

I have a SQL Server failover cluster with two nodes. Whenever a failover occurs, either manually or automatically, the log_table gets blocked for 30-50 minutes.

Why this kind of problem occurs on the only failover except failover there is no any blocking on this table? How can I intervene to end the blocking? What can I do to prevent it from happening again?

Here is the update_sp stored procedure:

    @ID varchar(32),
    @Resp varchar(max),
    @RespDate datetime,
    @ServTime float = null
AS
BEGIN
    UPDATE [dbo].[log_table]
    SET Resp = @Resp,
        RespDate = @RespDate,
        ServTime = DATEDIFF(MILLISECOND,RequestDate,@RespDate)
    WHERE
        ID = @ID AND
        RecordTime > DATEADD(HOUR, -1 , GETDATE())  
END 

Here is the insert_sp stored procedure:

ALTER PROCEDURE [dbo].[insert_sp]
    @ID varchar(32)
    @Resp varchar(max),
    @RequestDate datetime,
    @RespDate datetime,
    @ServTime float=null,
    @TranCode varchar(64) = NULL
AS
BEGIN
    INSERT INTO [dbo].[log_table] ([ID], [Resp], [RequestDate],[RespDate], [ServTime], TranCode)
     VALUES (@ID, @Resp, @RequestDate, @RespDate, @ServTime, @TranCode)
END

Note: I'm using SQL Server 2019 Enterprise (CU19)

I tried kill blocking SPID but every time occurs another id (no success) I did a recompile for these 2 stored procedures (no success) I tried to alter update_sp without altering operation, but it is already on blocking.(no success)

1
  • Can you provide more detail about the blocking? What's the blocking transaction? what's its state? what's the blocked transaction? Jun 26 at 19:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.