Killing the Session in SQL Server

Some time sessions are not killed in the sql server and if you want to restore the database then we are getting the single user thread to avoid this kill the session.

Below script will help you to kill the session.

DECLARE @sql varchar(8000)

DECLARE @sid VARCHAR(4000), @initCount INT =1,@count INT =0;

DECLARE @temp AS TABLE (spID INT)

INSERT INTO @temp

select est.session_id from sys.dm_tran_active_transactions tas

inner join sys.dm_tran_database_transactions tds on (tas.transaction_id = tds.transaction_id )

inner join sys.dm_tran_session_transactions est on (est.transaction_id=tas.transaction_id)

where est.is_user_transaction =1 and tas.transaction_state =2

and tds.database_transaction_begin_time is not null

SET @count = (SELECT COUNT(*) FROM @temp)

WHILE ( @initCount <= @count)

BEGIN

SELECT @sid=spID FROM @temp AS t WHERE spID=@initCount

SET @SQL = 'kill '+ @sid

EXEC(@SQL)

FETCH NEXT FROM c_trans INTO @sid

SET @initCount+=1;

END