Skip to content

Instantly share code, notes, and snippets.

@stevewithington

stevewithington/sql-kill-all-transactions.sql

Created August 6, 2021 12:55
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stevewithington/7a0f86b26c78d6a019534863c65f4772 to your computer and use it in GitHub Desktop.
Save stevewithington/7a0f86b26c78d6a019534863c65f4772 to your computer and use it in GitHub Desktop.
Download ZIP
Kill all transactions in a database during a restore to avoid issues with exclusive access.
Raw
sql-kill-all-transactions.sql
-- Kill all transactions in a database during a restore to avoid issues with exclusive access.
DECLARE @SQL VARCHAR(8000)
SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('AdventureWorks')
PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute
-- https://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment