Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Who's Blocking Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 3:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 15, 2014 6:55 PM
Points: 19, Visits: 53
Comments posted to this topic are about the item Who's Blocking
Post #401551
Posted Thursday, April 3, 2008 7:20 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 8:26 AM
Points: 537, Visits: 1,923
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
Posted Thursday, April 2, 2009 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:22 AM
Points: 3, Visits: 132
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 »

Add to briefcase

Permissions Expand / Collapse