kill all the transactions in a database


/ Published in: SQL
Save to your folder(s)

If you have ever tried to restore a database and could not obtain exclusive access, you know how useful this can be.


Copy this code and paste it in your HTML
  1. DECLARE @SQL VARCHAR(8000)
  2.  
  3. SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
  4. FROM sys.sysprocesses
  5. WHERE DBID=DB_ID('AdventureWorks')
  6.  
  7. PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute

URL: http://www.mssqltips.com/sqlservertip/1521/the-many-uses-of-coalesce-in-sql-server/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.