Is there any way to list out the locked tables and to kill the transactions if we want them to be unlocked immediately.
Or is there any other terminology do we need to follow for above operation i am seeking for.
Any Help or guidance will be appreciated.
Expand-server-management-double click Activity Monitor. on left side you have three options to choose from, select those options and you can see all the locks related information. run this stored procedure in the database.
This will show all databases with exclusive locks being held (which may include transient ones held at the time this is run), using the sys.dm_tran_locks
DMV:
select d.*, l.* from sys.dm_tran_locks l
join sys.databases d on l.resource_database_id = d.database_id
where l.request_mode = 'X'
(X = exclusive, S = Shared, IS = Intent Shared) See Lock Modes.
But probably the best way is to turn on Trace Flags 1204 and 1222:
Trace Flag 1204 and Trace Flag 1222 When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.
Ref: Detecting and Ending Deadlocks
Also, run sp_who2
and look for entries in the BlkBy
(Blocked By) column; follow these until you get to the head of the deadlock chain. That is the process identifier (or PID) responsible.
To get what sql is running behind a specific process you can run:
dbcc inputbuffer (@pid)
and use that PID to kill the Process (with prudence and at your own risk):
kill @pid
Check out Who is Active? v10.00: DMV Monitoring Made Easy
Also read Blocking is not Deadlocking (to distinguish the two scenarios)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With