May 9, 2023 at 8:20 am
Hi All,
I am trying to capture and set up alert for blocking when it goes more than 30 seconds.
Is the following correct OR maybe i need to do with wait time.
select @@servername [SERVER],sp.spid,sp.blocked, (CONVERT(DATETIME,CAST(sp.last_batch AS CHAR(8)),101)) as last_batch,sp.waittime,sp.waitresource,sp.lastwaittype,sp.cmd, sp.dbid,
sp.loginame,sp.hostname,sp.cpu
from master.dbo.sysprocesses sp
where sp.spid>50 and sp.spid<>sp.blocked and sp.blocked<>0 and datediff(Second,last_batch,GETDATE())>30
0 seconds.
May 9, 2023 at 8:39 am
You can capture blocking with extended events on SQL 2019, but doesn't have alerting
Alerting can be done with xesmarttarget
https://spaghettidba.com/2022/02/28/recipe-5-killing-blocking-spids/
https://spaghettidba.com/2022/02/25/recipe-4-sending-alerts-via-email/
May 10, 2023 at 1:58 pm
Thanks, it is little complicated to configure https://spaghettidba.com/2022/02/28/recipe-5-killing-blocking-spids/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply