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

Answer 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.

Answer 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