sysprocesses and blocking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sysprocesses and blocking

Author

Topic

cottage125
Starting Member

32 Posts

Posted - 2008-07-18 : 01:38:43

SELECT p1.spid,
p1.blocked,
p1.cmd,
p1.sql_handle,
p1.stmt_start/2 AS CodeStart,
CASE WHEN p1.stmt_end = -1 THEN -1
ELSE p1.stmt_end/2 END AS CodeEnd,
p1.DBID
INTO #Block
from sys.sysprocesses p1
where blocked<>0
and spid >51
After running this query I m getting this main columns(not all included here)
Spid Blocked Cmd
54 53 Update

These are the few columns of sysprocesses table.
Right now I can see that 54 is blocked by 53.
I can see that 54 is updating.
But I have 1000s of blocking and I want to create an automated script which catch this information.
I want to make an automated script which understands that 53 is the culprit for 54 and gives details of what command 53 is running?
So I want the cmd,and sqL_handle(statement) information for column 1 and column 2 both.
we can use sysprocesses table and make some join but I m not sure how it works?
Thanks and I really appreciate ur help

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-18 : 02:55:16

Asked and answered
[url]http://www.sqlservercentral.com/Forums/Topic536467-145-1.aspx[/url]

--
Gail Shaw
SQL Server MVP

cottage125
Starting Member

32 Posts

Posted - 2008-07-18 : 03:05:27

thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources