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)