In this article, we have divided into 5 steps:
1. Blocking Definition
2. Query to check blocking in Sybase
3. Get the showplan(query plan) in Sybase
4. Get the SQLText of Sybase process ID.
5. How to kill the Sybase process id which is causing blocking.
1. Blocking Definition:
“Blocking” means that one connection is holding a lock on a resource when another connection wants to read or write to it.
2. 2. Query to check blocking in Sybase
We have to run this query in master database.
use master go
select spid,cmd,blocked,time_blocked from sysprocesses where blocked>0 go
Output:
spid cmd blocked time_blocked ------ ------------------------------------------------------------ ------- ------------ 32 SELECT 31 21
Which means process 31 is blocked the process 32. So, until process id 31 completes 32 cannot be executed.
3. Check the show plan(query plan) of the process id 31.
sp_showplan 31,null,null,null go
Output:
QUERY PLAN FOR STATEMENT 3 (at line 3). STEP 1 The type of query is WAITFOR. (return status = 0)
4. Now check the sql text of process id 31.
To verify the sql text, we have enable the dbcc trace 3604 by executing below command.
dbcc traceon(3604) go
dbcc sqltext(31) go
Output:
SQL Text: BEGIN TRANSACTION SELECT * FROM emp WAITFOR DELAY '00:05:00' ROLLBACK TRANSACTION DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
5. Kill process ID
Important note: If you are in production environment, you have to take full approvals from application or respective team to kill the blocked process by explains consequences of killing a process id.
kill 31 go
Finally verify the blocking executing same query which we ran step 2.
Same explained in the following video, please subscribe my channel.