Who's Blocking

• Eric Wahner-345205

  Mr or Mrs. 500

  Points: 544

  More actions

  September 23, 2007 at 3:41 am

  #174933

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

• DBA-640728

  SSChampion

  Points: 12898

  More actions

  April 3, 2008 at 7:20 am

  #797784

  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.

• gperuch

  Valued Member

  Points: 55

  More actions

  April 2, 2009 at 6:03 am

  #970561

  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