Rollback trans and unlock tables on SQL Server

Question

Hy , sometimes I had problems with SQL Server because of unknown transactions left opened or a lock table, and I read many suggestion to avoid restarting the server
until I found this , and it seems to work
do you have others suggestion to release locks and rollback trans ?
because I am reserved on running this on a production server

USE master;
GO
ALTER DATABASE [db_dev]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [db_dev]
SET MULTI_USER;
GO

Solution 1

You can check what is blocking your process by using

sp_who2

then

kill spid

setting it to single_user then reverting back will drop all connections to that database. Can be very dangerous on production servers.

Solution 2

If you have lock on a table you can kill that with below code :

    SELECT OBJECT_NAME(P.object_id) AS TableName,
           Resource_type,
           request_session_id
    FROM sys.dm_tran_locks AS L
         JOIN sys.partitions AS P ON L.resource_associated_entity_id = p.hobt_id
    WHERE OBJECT_NAME(P.object_id) = '<Table_Name>';

    GO
    Kill session_ID

Comments

[Pondlife]

Have you identified what is holding the locks? You can KILL an individual connection without restarting the server or taking the database offline.

[Paul Carlton]

This doesn't work when sys.dm_tran_locks is also locked.