Blocking process and its eventinfo

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Blocking process and its eventinfo

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2010-06-07 : 09:02:40

Hi All,

I am stucked somewhere creating the scripts and trying to scratch my head to get it done ..I have implemented half of the logic for my requirement but got stucked..

I have a requirement to get the all running processes which are blocked by the another processes and blocking query as well.We are going to schedule this script for every 5 min. while our data loads run..

I want to create one table which should contain the columns

SPID
Status
HostName
BlkBy
DBName
eventinfo -This column value must come from the output of dbcc inputbuffer(blkby)

I am pasting my effort for this requirement please suggest me on this

-- SQL 2000/2005/2008
CREATE TABLE #sp_who(
SPID char(5) null
,Status varchar(200) null
,Login sysname null
,HostName varchar(100) null
,BlkBy char(5) null
,DBName sysname null
,Command nvarchar(300) null
,CPUTime varchar(30) null
,DiskIO varchar(30) null
,LastBatch varchar(30) null
,ProgramName varchar(500) null
,SPID2 char(5) null
)
IF (select cmptlevel from master..sysdatabases where dbid =1 )>=90
ALTER TABLE #sp_who ADD Requestid char(5) null
--truncate table #sp_who2
insert into #sp_who exec sp_who2
select * into #sp_who2 from #sp_who where blkby !=' . '
select * from #sp_who2
--select * from #sp_who2 where blkby !=' . '
--drop table #sp_who2

declare @SPID char(5)
declare @Status varchar(200)
declare @HostName varchar(100)
declare @BlkBy char(5)
declare @DBName sysname
declare @Dbcc varchar(2000)

declare blk cursor local static for select SPID,Status,HostName,BlkBy,DBName from #sp_who2

open blk

fetch next from blk into @SPID,@Status,@HostName,@BlkBy,@DBName

while @@fetch_status = 0

if @SPID <> @BlkBy

set @Dbcc = 'dbcc inputbuffer('+ @BlkBy +')'
exec (@dbcc)

-----Here i am confuse this dynamic sql will give three column as an output but i want to take
--only eventinfo column for the respective blocking process and to add

I hope i explain my problem correctly

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-06-07 : 13:48:26

This would create a temp table , place the output of the exec , and you can then return what values are required

create table #temp1
(col1 int,col2 int,col 3)
insert into #temp1 exec(@dbcc)

select col1 from #temp1

drop table #temp1

Jack Vamvas
--------------------
http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources