Who's Blocking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Eric Wahner » Who's Blocking

Who's Blocking

Rate Topic

Display Mode

Topic Options

Author

Message

Eric Wahner-345205

Posted Sunday, September 23, 2007 3:41 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, February 07, 2013 7:52 AM
Points: 19, Visits: 51

Comments posted to this topic are about the item Who's Blocking

Post #401551

DBA-640728

Posted Thursday, April 03, 2008 7:20 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:21 PM
Points: 496, Visits: 1,724

i tried running this script , right click and click in output to text but i get this error

Msg 213, Level 16, State 7, Line 4
Insert Error: Column name or number of supplied values does not match table definition.

Post #479173

gperuch

Posted Thursday, April 02, 2009 6:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:36 AM
Points: 3, Visits: 87

try this:

set nocount on
declare @spid varchar(10)
declare @blkby varchar(10)
declare @stmt varchar(100)

create table #temp
( spid integer, status varchar(100), login varchar(50), hostname varchar(25), blkby varchar(10),
dbname varchar(25), command varchar(100), cputime int, diskio int, lastbatch varchar(25),
programname varchar(255), spid2 int)
create table #temp2005
( spid integer, status varchar(100), login varchar(50), hostname varchar(25), blkby varchar(10),
dbname varchar(25), command varchar(100), cputime int, diskio int, lastbatch varchar(25),
programname varchar(255), spid2 int, requestid int)

if @@version like 'Microsoft SQL Server 2000%' insert into #temp exec sp_who2
if @@version like 'Microsoft SQL Server 2005%' insert into #temp2005 exec sp_who2

insert into #temp select spid, status ,login, hostname , blkby ,
dbname, command , cputime , diskio , lastbatch ,
programname , spid2 from #temp2005

declare curs cursor for
select convert(varchar(10),spid), blkby from #temp where blkby not like '%.%'

open curs

fetch next from curs into @spid, @blkby
while @@fetch_status = 0
begin
set @stmt = 'dbcc inputbuffer(' + @blkby + ')'
raiserror('SPID:%s is Blocking with the following statement',0,1,@blkby) with nowait
exec (@stmt)
raiserror('SPID that is Blocked:%s',0,1,@spid) with nowait
set @stmt = 'dbcc inputbuffer(' + convert(varchar(10), @spid) + ')'
exec (@stmt)
fetch next from curs into @spid, @blkby
end

close curs

deallocate curs

drop table #temp
drop table #temp2005

Post #688751

« Prev Topic | Next Topic »

Permissions