How to check blocking in Sybase

Blocking means holding a lock on resource when another connection wants to read or write to it.

Checking blocking process:

1> use master
2> go
1> select spid, blocked from sysprocesses where blocked>0
2> go


1> select spid,blocked, db_name(dbid) db_name from sysprocesses where blocked >0
2> go
spid blocked db_name
------ ------- -------
242 162 k2_db
101 341 k2_db
457 341 k2_db
(3 rows affected)

Here process id 242 is blocked by 162 and processes 101 and 457 ae blocked by 341

To check process details of blocking spid

1> select spid,cmd,physical_io, db_name(dbid) db_name from sysprocesses where spid=162
2> go
spid cmd physical_io db_name
------ ---------------- ----------- -------
341 REORG 12121238 k2_db
(1 row affected)

To get the query plan or show

1>use master
1>select * from sysprocesses where spid=162
1>sp_who “162”
1>dbcc traceon(3604)
1>dbcc sqltext(162)
1>sp_showplan 162,null,null,null

Related Posts

You can leave a response, or trackback from your own site.

Leave a Reply

Powered by k2schools
%d bloggers like this: