Query and kill sessions with locks in SQL server.

kill-locks.sql

DECLARE @DatabaseName NVARCHAR(MAX) = 'your_database_name'

SELECT DISTINCT('KILL ' + CONVERT(varchar(10), request_session_id))
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID(@DatabaseName)

-- Adapted from https://blog.sqlauthority.com/2016/05/25/sql-server-fix-error-1807-not-obtain-exclusive-lock-database-model-retry-operation-later-part-2/

query-locks.sql

DECLARE @DatabaseName NVARCHAR(MAX) = 'your_database_name'

IF EXISTS (
	SELECT * 
	FROM sys.dm_tran_locks
	WHERE resource_database_id = DB_ID(@DatabaseName)
)
BEGIN
    PRINT 'Database "' + @DatabaseName + '" in use!'
 
    SELECT *
    FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_tran_locks l ON s.session_id = l.request_session_id
    WHERE l.resource_database_id = DB_ID(@DatabaseName)
END
ELSE
    PRINT 'Database "' + @DatabaseName + '" not in use.'

-- Adapted from https://blog.sqlauthority.com/2016/05/25/sql-server-fix-error-1807-not-obtain-exclusive-lock-database-model-retry-operation-later-part-2/