How to find locked tables and kill the session in Microsoft SQLServer?
- Open SQL Server Management Studio (SSMS).
- Connect to the SQL Server instance.
- Open a new query window.
- Execute the following query to find the sessions that have locks on tables:
SELECT session_id, blocking_session_id, wait_type, wait_time, last_wait_type, wait_resource, transaction_isolation_level, program_name, login_name, status, cpu_time, total_elapsed_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
This query will return a list of sessions that are blocking other sessions.
- Identify the session you want to kill by looking at the session_id column.
- Execute the following command to kill the session:
with the session ID you want to kill.
- Verify that the session has been killed by executing the following command:
SELECT session_id, status FROM sys.dm_exec_sessions WHERE session_id = <session_id>
with the session ID you killed. The query should return no rows if the session has been successfully killed.
Note: Killing a session will terminate the session immediately and release all locks held by the session. However, it can also cause data inconsistency if the session was performing a critical operation. Use caution when killing sessions and ensure that it is the appropriate action.
Leave a comment