October 27, 2008 at 8:30 pm
I am trying to configure a performance alert to detect any long running blocked process.
For this alert to be trigerred, do we need to configure a job as well
and schedule it accordingly. Please provide your inputs.
Also, I want to get only the blocks.
Please advice.
M&M
October 27, 2008 at 11:03 pm
This is somewhat similar to your requirement it also kills the bad session ....
Just set it accordingly to fit your requirement.
DECLARE @spid int
DECLARE @sqlStr nvarchar(255)
DECLARE @DBCC VARCHAR(100)
SELECT @spid=spid FROM sysprocesses where
last_batch < dateadd(mm,-180, getdate()) -- last_batch longer than 180 minutes
--SELECT @sqlStr=N'KILL ' + CAST(@spid as nvarchar(5))
SELECT @sqlStr=CAST(@spid as nvarchar(5))
SET @DBCC = 'DBCC INPUTBUFFER ('+@sqlStr+')'
SELECT @DBCC
IF @spid IS NOT NULL
EXEC sp_executesql @DBCC
October 28, 2008 at 1:01 am
1) select * from master..sysprocesses where blocked > 1 and waittime > xxx
2) Scheduling a job is required.
3) Ignore self blocking (blocking by same SPID)
October 28, 2008 at 3:07 am
1) go to SQL Agent -> alerts
2) right click on alert and select "New Alert"
3) On tab "general", set:
- name: process blocked
- type : SQL Server performance condition alert
- object: SQL server:general statistics
- counter: Processes blocked
- alert if counter rises above 0
4) On tab "Response" fill in your job
Wilfred
The best things in life are the simple things
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply