— Replace ‘YourDatabaseName’ with the name of your database
— Replace ‘YourTableName’ with the name of your table
USE YourDatabaseName;
GO
DECLARE @TableName NVARCHAR(128) = ‘YourTableName’;
— Find the SPIDs accessing the specific table
SELECT DISTINCT r.session_id
INTO #SessionsToKill
FROM sys.dm_tran_locks AS l
JOIN sys.partitions AS p ON l.resource_associated_entity_id = p.hobt_id
JOIN sys.objects AS o ON p.object_id = o.object_id
JOIN sys.dm_exec_requests AS r ON l.request_owner_id = r.request_id
WHERE o.name = @TableName AND o.type = ‘U’;
— Declare a cursor to iterate through the SPIDs and kill them
DECLARE @session_id INT;
DECLARE kill_cursor CURSOR FOR
SELECT session_id FROM #SessionsToKill;
OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id;
WHILE @@FETCH_STATUS = 0
BEGIN
— Kill each session accessing the table
EXEC(‘KILL ‘ + @session_id);
FETCH NEXT FROM kill_cursor INTO @session_id;
END
CLOSE kill_cursor;
DEALLOCATE kill_cursor;
— Drop the temporary table
DROP TABLE #SessionsToKill;
