Link to home
Get AccessLog in
Avatar of roadtrain64
roadtrain64

asked on

Sysprocesses and blocking

     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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you have to self-join
 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
                  p2.cmd cmd2,
                  p2.sql_handle sql_handle2,
                  INTO #Block      
from sys.sysprocesses p1
join sys.sysprocesses p2
  on p2.spid = p1.blocked
where p1.blocked<>0
and p1.spid >51

Open in new window

Avatar of roadtrain64
roadtrain64

ASKER

Hi Thanks Right now i can see commands of blocked spid but actually what i want is blockind spid's command. so that i can understand what is blocking what and i dont think only spid is enogh to get this since i want to make the process automated. Below is the code that i made for this purpose buti m getting some error.
  plz guide me where is error and how to correct thanks
DECLARE c_Blockers CURSOR FAST_FORWARD FOR
				  SELECT	Spid,
                            blocked,
							Cmd1,
							sql_handle1,
							Start1,
							End1,
							DBID,
                            Cmd2,
							sql_handle2,
							Start2,
							End2
					FROM	#Blockers
 
				OPEN c_Blockers
				FETCH c_Blockers INTO @Spid, @blocked, @Cmd1, @sql_handle1, @Start1, @End1, @DBID, @Cmd2, @sql_handle2, @Start2, @End2
 
		        WHILE @@FETCH_STATUS = 0 BEGIN
/* create table SA_Trace_Blockers(Snapshotdate datetime, spid int, blocked int, cmd1 varchar(250),
                                  statement1 varchar(3000), BlockingCode1 varchar(max), dbid int,cmd2 varchar(250),
                                  statement2 varchar(3000), BlockingCode2 varchar(max))
drop table SA_Trace_Blockers*/
 
					INSERT INTO SA_Trace_Blockers(SnapshotDate, Spid, blocked, Cmd1, Statement1, BlockingCode1, DBID, Cmd2, Statement2, BlockingCode2)
						SELECT	@SnapshotDate,
								@Spid,
                                @blocked,
								@Cmd1,
								Text,
								SUBSTRING( text,  COALESCE(NULLIF(@Start1, 0), 1), CASE @End1 
									WHEN -1 
										THEN DATALENGTH(text) 
									ELSE 
										(@End1 - @Start1) 
									END ),
                                @DBID,
                                @cmd2,
                                Text,
								SUBSTRING( text,  COALESCE(NULLIF(@Start2, 0), 1), CASE @End2 
									WHEN -1 
										THEN DATALENGTH(text) 
									ELSE 
										(@End2 - @Start2) 
									END )
                               
						FROM	::fn_get_sql (@sql_handle1)
		
					FETCH c_Blockers INTO @Spid, @blocked, @Cmd1, @sql_handle1, @Start1, @End1, @DBID, @Cmd2, @sql_handle2, @Start2, @End2
				END
 
				CLOSE c_Blockers
				DEALLOCATE c_Blockers
			END
		END 
	END

Open in new window

my command above should give you cmd and cmd2 ... (from p1.cmd and p2.cmd)

don't use a cursor when you don't need it :)
ya ur code gives me cmd1 and cmd2. but from what SP those commands comes from?? i can analyze that from sql_handle and SQL_handle2 i guess by using stmt_start and stmt_end properly but i m not sure how to use that exactly?? can u suggest that please???

Thanks
I though you would "get" what my query is doing,

p1 is the blocked process
p2 refers to the blocking process

I added the other 2 columns CodeStart2 and CodeEnd2 to the query.
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
                  p2.cmd cmd2,
                  p2.sql_handle sql_handle2,
                  p2.stmt_start/2 AS CodeStart2,
                  CASE WHEN p2.stmt_end = -1 THEN -1 
                  ELSE p2.stmt_end/2 END AS CodeEnd2,
                  INTO #Block      
from sys.sysprocesses p1
join sys.sysprocesses p2
  on p2.spid = p1.blocked
where p1.blocked<>0
and p1.spid >51

Open in new window

ya  i got what ur quert gives plz see the attached file
now in this query i can get commands  so thats good but
i want from where or from which script these commands comes from?? and
that we can find from sql_handle field and stmt_start and stmt_end field
but how?? how to manipulate that information???
this is my question. i want them in simpliefiled form.
  is that possible??
Thanks a lot
in sysprocesses, you have other fields like hostname, program_name.
is that what you need?
no  i think my question is very clear what i want...
>no  i think my question is very clear what i want...
sorry, but I don't actually "see" what you are "missing" :(

eventually, because there is no attached file although you speak of one in your previous comment...


i m so sorry for that angel  now find this file here
i want to see text instead of sql handle for both sql_handle1 and sql_handle2
thanks
untitled.bmp
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access