Who's Blocking

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

  • 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.

  • 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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply