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)