SQL2005 blocking

SQL2005 server was blocking, running at 100% of processor, and the program
didn't work.
So I go to activity monitor to see, hat is going there.
When I opened the only active proccess on my database, I saw the following
TSQL command batch:

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters]
int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(77)')
select [Event Info] from #tmpDBCCinputbuffer

and I couldn't kill it.

I guess this is something internal and is not the real reason.

Does anybody know, hot to get the blocking sql commands on sql2005?
Probably with some sys views?
Any example?

Thanks,
Simon

simonZ wrote:
> SQL2005 server was blocking, running at 100% of processor, and the program
> didn't work.
> So I go to activity monitor to see, hat is going there.
> When I opened the only active proccess on my database, I saw the following
> TSQL command batch:
>
> create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters]
> int, [Event Info] nvarchar(512))
> insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(77)')
> select [Event Info] from #tmpDBCCinputbuffer
>
> and I couldn't kill it.
>
> I guess this is something internal and is not the real reason.
>
> Does anybody know, hot to get the blocking sql commands on sql2005?
> Probably with some sys views?
> Any example?
>
> Thanks,
> Simon
>
>

Open a new query window, and run this query:

EXEC sp_who2

Review the output of that command - one of the columns returns is BlkBy,
and for any processes that are being blocked, this column will show you
the spid that is blocking it.


--
Tracy McKibben
MCDBA
http://www.realsqlguy.com