Stop blocking before it gets out of hand?
-
Guys,
We had some issues here - a certain process was blocking other processes, things were hanging and caused problems. I am now thinking to set up some type of monitoring to prevent this in the future.
The goal is to be able to tell which processID is blocking which processID for > 't' amount of time and send an alert if such a situation occurs.
Alerting I know how to do. What I am not clear about is how to identify this issue.
Can someone suggest a way of doing it?
I am currently exploring how I can utilize master..sysprocesses for this purpose, but cannot figure out best way of achieving this goal.
Any suggestions?
Thanks in advance!
-
you will have to take periodical "snapshots" of sysprocess and look the blkby column with the lastbatch datetime. By diffing the lastbatch and current time you will probably achieve what you want.
Hope this helps.
* Noel -
Noel,
I found something similar to what you have suggested here: http://www.databasejournal.com/features/mssql/article.php/3426431
although, the solution appears a bit more complicated than I need it to be ...
Thank you
-
Correct!
Just leave out all those calls to sp_OA* procedures
You only need pure TSQL
Good Luck!
* Noel -
There are quite a few versions of Block Monitoring out on the web. Here is one that I found and doctored up a bit. I have the procedure running in a job every n minutes, writing to a table.
CREATE
TABLE [dbo].[Lock_monitor](
[pkid] [int]
IDENTITY(1,1) NOT NULL,
[Occurs] [datetime]
NOT NULL CONSTRAINT [DF_Lock_monitor_Occurs] DEFAULT (getdate()),
[Proc_blocked] [int]
NULL,
[Proc_blocking] [int]
NULL,
[Qry_blocked] [varchar]
(255) NULL,
[Qry_blocking] [varchar]
(255) NULL,
[User_Blocking] [varchar]
(100) NULL,
[WaitResource] [varchar]
(1000) NULL,
[WaitTime] [int]
NULL DEFAULT (0),
PRIMARY
KEY CLUSTERED
(
[pkid]
ASC
)
GO
CREATE
PROCEDURE [dbo].[MONITOR_LOCKS] as
create
table #procs (eventtype varchar(60), Parameters int , EventInfo varchar(600))
declare
@cmd varchar(250), @spid_1 int, @spid_2 int, @qry1 varchar(255), @qry2 varchar(255), @user1 varchar(100), @user2 varchar(100)
declare
@id int, @WaitTime int, @WaitResource varchar(100)
select
spid, blocked, loginame = rtrim(loginame), waittime, waitresource = rtrim(waitresource)
into
#temp_proc
from
master.dbo.sysprocesses where blocked > 0 and waittime > 3000
declare
CUR1 cursor for
select
spid, blocked, rtrim(loginame), WaitTime, rtrim(WaitResource) from #temp_proc
open
CUR1
fetch
next from CUR1 into @spid_1, @spid_2, @user1, @WaitTime, @WaitResource
while
@@fetch_status = 0
BEGIN
truncate table #procs
set @cmd = (select 'dbcc inputbuffer(' + cast(@spid_1 as varchar) + ')')
insert into #procs (eventtype, Parameters, EventInfo)
exec (@cmd)
Insert into dbo.Lock_monitor (Proc_blocked, Proc_blocking, Qry_blocked, [User_Blocking], WaitTime, WaitResource)
select @spid_1, @spid_2, rtrim(EventInfo), rtrim(@user1), @WaitTime, rtrim(@WaitResource)
from #procs
set @id = scope_identity()
truncate table #procs
set @cmd = (select 'dbcc inputbuffer(' + cast(@spid_2 as varchar) + ')')
insert into #procs (eventtype, Parameters, EventInfo)
exec (@cmd)
update dbo.Lock_monitor
set Qry_blocking = IsNull((select rtrim(EventInfo) from #procs), ''),
proc_blocked
= @spid_1,
proc_blocking
= @spid_2,
user_blocking
= rtrim(@user1)
where pkid = @id
fetch next from CUR1 into @spid_1, @spid_2, @user1, @WaitTime, @WaitResource
END
CLOSE
CUR1
DEALLOCATE
CUR1
return
GO
Regards,
Rubes -
here's mine....
I run it from sqlagent outputting to a file. that way I have a history if the block is gone by the time I get there. It gives enough info to investigate the blocking code and who was affected. I choose to trap blocks lasting 30secs + but you can tune that. Set yourself up jobs to stop/start the job once a day and cycle the output log. It works for 7,2000,2005
if exists (select 1 from sysobjects where type = 'P' and name = 'up_blocker_check_loop')
begin
drop proc up_blocker_check_loop
end
go
create procedure up_blocker_check_loop @time_delay char(8) = '00:00:30'
as
declare @spid char(4), @blocker char(4),
@spid2 char(4), @blocker2 char(4),
@head_of_chain char(4), @head_of_chain2 char(4),
@time datetime, @found int, @blocked_users int
DECLARE @Handle binary(20)
create table #inputbuffer( EventType varchar(40) not null,
Parameters varchar(40)not null,
EventInfo varchar(255) null)
/*******************************************************************************************************/
/* read ahead to set up initial blocking information if present */
/********************************************************************************************************/
select @head_of_chain = ' '
set @found = 0
set nocount on
declare block_cursor cursor for
select distinct "Spid"=convert(char(4),spid),
"Block"=convert(char(4),blocked)
from master..sysprocesses
where blocked != 0
order by block, spid
open block_cursor
if @@cursor_rows = 0 -- no block found
goto loopstart
fetch block_cursor into @spid, @blocker
while @@fetch_status = 0
begin
select 'spid ', @spid, 'is blocked by ', @blocker
insert into #inputbuffer
exec ('dbcc inputbuffer (' + @spid + ')')
select 'SQL issued by blocked process ', @spid, ' is'
-- exec ('dbcc inputbuffer (' + @spid + ')')
if (select eventtype from #inputbuffer) = 'rpc event'
begin
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses
WHERE spid = @spid
SELECT * FROM ::fn_get_sql(@Handle)
end
else
select eventinfo from #inputbuffer
truncate table #inputbuffer
fetch block_cursor into @spid, @blocker
end
close block_cursor
deallocate block_cursor
-- find out spid at top of blocking chain
select @head_of_chain =
(select max(spid) from master..sysprocesses where blocked = 0 and
(spid in (select blocked from master..sysprocesses where blocked !=0)))
print 'spid at head of blocking chain = ' +@head_of_chain
loopstart:
/***************************************************************************************************/
/* never ending loop comparing current block to previous block and reporting details if one found */
/***************************************************************************************************/
while 1 != 0
begin
declare block_cursor2 cursor for
select distinct "Spid"=convert(char(4),spid),
"Block"=convert(char(4),blocked)
from master..sysprocesses
where blocked != 0
order by block, spid
open block_cursor2
fetch block_cursor2 into @spid2, @blocker2
while @@fetch_status = 0
begin
if @found = 30
begin
select 'spid ', @spid2, 'is blocked by ', @blocker2
insert into #inputbuffer
exec ('dbcc inputbuffer (' + @spid2 + ')')
select 'SQL issued by blocked process ', @spid2, ' is'
-- exec ('dbcc inputbuffer (' + @spid2 + ')')
if (select eventtype from #inputbuffer) = 'rpc event'
begin
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses
WHERE spid = @spid2
SELECT * FROM ::fn_get_sql(@Handle)
end
else
select eventinfo from #inputbuffer
truncate table #inputbuffer
set @blocked_users = @@cursor_rows
end
fetch block_cursor2 into @spid2, @blocker2
end
close block_cursor2
deallocate block_cursor2
select @head_of_chain2 =
(select max(spid) from master..sysprocesses where blocked = 0 and
(spid in (select blocked from master..sysprocesses where blocked !=0)))
if @head_of_chain != @head_of_chain2
set @found = 0 -- force a 30 sec pause when block changes
if @head_of_chain = @head_of_chain2 and @found = 30 -- same block present as in last check for 30 seconds
begin
set @time = (select getdate())
print '********************************************************************** '
print 'time now is ' +convert(varchar(30),@time) +' and ' +convert(char(2),datepart(ss,@time)) +' seconds'
print '********************************************************************** '
insert into #inputbuffer
exec ('dbcc inputbuffer (' + @head_of_chain2 + ')')
select 'SQL issued by blocking process ', @head_of_chain2, ' is'
-- exec ('dbcc inputbuffer (' + @head_of_chain2 + ')')
if (select eventtype from #inputbuffer) = 'rpc event'
begin
SELECT @Handle = sql_handle FROM master.dbo.sysprocesses
WHERE spid = @head_of_chain2
SELECT * FROM ::fn_get_sql(@Handle)
end
else
select eventinfo from #inputbuffer
-- raiserror ('**** Blocking is occuring by spid %s ****', 16, 1, @head_of_chain2) with log
print ''
select convert(char(10),nt_username) as 'NT User Name', last_batch, db_name(dbid) as 'database'
from master..sysprocesses
where spid = @head_of_chain2
print 'Distinct objects blocks are held on'
select distinct rsc_objid As ObjId,
rsc_indid As IndId,
convert (smallint, req_spid) As spid
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and req_spid = @head_of_chain2
print '************************************************************************************************* '
truncate table #inputbuffer
waitfor delay @time_delay
goto loopend
end
waitfor delay '00:00:01' -- delay to cut down resource usage of this job
loopend:
set @head_of_chain = @head_of_chain2
if @head_of_chain is null
set @found = 0
else
if @found < 30
set @found = @found + 1
end
go
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply