Problem: I have faced many times from support team that they are facing timeouts from database server and database team needs to look at it and resolve the issue as soon as possible.
I have list of blogs available on net and on my blog site that can detect deadlocks from Profiler but I need to find blocking processes from Query and kill that process if it is not critical.
To do that I got following Queries.
=============================================
select top 5 cmd,* from sys.sysprocesses
where blocked > 0
select * from sys.dm_tran_locks
exec sp_lock
Once decided use this command to kill process
Kill (Process ID)
==============================================
Blocked column shows data "1" or "True" in case of blocking process. Once you find blocked process look at the "cmd" column values and decide if you need to kill that process. 2nd and 3rd query also shows locking queries. Following are the result set of queries.
I have list of blogs available on net and on my blog site that can detect deadlocks from Profiler but I need to find blocking processes from Query and kill that process if it is not critical.
To do that I got following Queries.
=============================================
select top 5 cmd,* from sys.sysprocesses
where blocked > 0
select * from sys.dm_tran_locks
exec sp_lock
Once decided use this command to kill process
Kill (Process ID)
==============================================
Blocked column shows data "1" or "True" in case of blocking process. Once you find blocked process look at the "cmd" column values and decide if you need to kill that process. 2nd and 3rd query also shows locking queries. Following are the result set of queries.
No comments:
Post a Comment