CODEWITHSUNDEEP
sql-servert-sqltransactions
Mursa Catalin
Mursa Catalin

Reputation: 1449

Rollback trans and unlock tables on SQL Server

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

Upvotes: 4

Views: 20726

Answers (2)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12575

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

Upvotes: 2

sam yi
sam yi

Reputation: 4934

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.

Upvotes: 10

Related Questions