Script to kill all connections to a database More than RESTRICTED USER ROLLBACK

Question

I have a development database that re-deploy frequently from a Visual Studio Database project  via a TFS Auto Build    Sometimes when I run my build I get this error   ALTER DATABASE failed because a lock could not be placed on database  MyDB   Try again later    ALTER DATABASE statement failed    Cannot drop database  MyDB  because it is currently in use      I tried this   ALTER DATABASE MyDB SET RESTRICTED USER WITH ROLLBACK IMMEDIATE   but I still cannot drop the database    My guess is that most of the developers have dbo access    I can manually run SP WHO and start killing connections  but I need an automatic way to do this in the auto build    Though this time my connection is the only one on the db I am trying to drop    Is there a script that can drop my database regardless of who is connected

User · Answer

You can use Cursor like that   USE master GO  DECLARE  SQL AS VARCHAR 255  DECLARE  SPID AS SMALLINT DECLARE  Database AS VARCHAR 500  SET  Database    AdventureWorks2016CTP3   DECLARE Murderer CURSOR FOR SELECT spid FROM sys sysprocesses WHERE DB NAME dbid     Database  OPEN Murderer  FETCH NEXT FROM Murderer INTO  SPID WHILE   FETCH STATUS   0      BEGIN     SET  SQL    Kill     CAST  SPID AS VARCHAR 10             EXEC   SQL      PRINT    Process     CAST  SPID AS VARCHAR 10      has been killed      FETCH NEXT FROM Murderer INTO  SPID     END   CLOSE Murderer DEALLOCATE Murderer   I wrote about that in my blog here  http   www pigeonsql com single-post 2016 12 13 Kill-all-connections-on-DB-by-Cursor

User · Answer

Little known  the GO sql statement can take an integer for the number of times to repeat previous command   So if you   ALTER DATABASE  DATABASENAME  SET SINGLE USER GO   Then   USE  DATABASENAME  GO 2000   This will repeat the USE command 2000 times  force deadlock on all other connections  and take ownership of the single connection   Giving your query window sole access to do as you wish

User · Answer

Updated  For MS SQL Server 2012 and above  USE  master    DECLARE  kill varchar 8000          SELECT  kill    kill    kill     CONVERT varchar 5   session id          FROM sys dm exec sessions WHERE database id    db id  MyDB    EXEC  kill     For MS SQL Server 2000  2005  2008  USE master   DECLARE  kill varchar 8000   SET  kill         SELECT  kill    kill    kill     CONVERT varchar 5   spid          FROM master  sysprocesses   WHERE dbid   db id  MyDB    EXEC  kill

User · Answer

I have tested successfully with simple code below  USE  master  GO ALTER DATABASE  YourDatabaseName  SET SINGLE USER WITH ROLLBACK IMMEDIATE GO

User · Answer

USE MASTER GO   DECLARE  Spid INT DECLARE  ExecSQL VARCHAR 255    DECLARE KillCursor CURSOR LOCAL STATIC READ ONLY FORWARD ONLY FOR SELECT  DISTINCT SPID FROM    MASTER  SysProcesses WHERE   DBID   DB ID  dbname     OPEN    KillCursor   -- Grab the first SPID FETCH   NEXT FROM    KillCursor INTO     Spid   WHILE     FETCH STATUS   0     BEGIN         SET      ExecSQL    KILL     CAST  Spid AS VARCHAR 50             EXEC      ExecSQL            -- Pull the next SPID         FETCH   NEXT          FROM    KillCursor          INTO     Spid       END   CLOSE   KillCursor   DEALLOCATE  KillCursor

User · Answer

Matthew s supremely efficient script updated to use the dm exec sessions DMV  replacing the deprecated sysprocesses system table   USE  master   GO  DECLARE  Kill VARCHAR 8000         SELECT      Kill    Kill    kill     CONVERT VARCHAR 5   session id        FROM     sys dm exec sessions WHERE     database id   DB ID   lt YourDB gt      EXEC sys sp executesql  Kill    Alternative using WHILE loop  if you want to process any other operations per execution    USE  master   GO  DECLARE  DatabaseID SMALLINT   DB ID N  lt YourDB gt         DECLARE  SQL NVARCHAR 10    WHILE EXISTS   SELECT                 1                FROM                 sys dm exec sessions                WHERE                 database id    DatabaseID           BEGIN          SET  SQL                         SELECT TOP 1                         N kill     CAST session id AS NVARCHAR 5                             FROM                         sys dm exec sessions                     WHERE                         database id    DatabaseID                               EXEC sys sp executesql  SQL      END

User · Answer

The accepted answer has the drawback that it doesn t take into consideration that a database can be locked by a connection that is executing a query that involves tables in a database other than the one connected to   This can be the case if the server instance has more than one database and the query directly or indirectly  for example through synonyms  use tables in more than one database etc   I therefore find that it sometimes is better to use syslockinfo to find the connections to kill   My suggestion would therefore be to use the below variation of the accepted answer from AlexK   USE  master    DECLARE  kill varchar 8000          SELECT  kill    kill    kill     CONVERT varchar 5   req spid          FROM master dbo syslockinfo WHERE rsc type   2 AND rsc dbid    db id  MyDB    EXEC  kill

User · Answer

You should be careful about exceptions during killing processes  So you may use this script   USE master  GO  DECLARE  kill varchar max         SELECT  kill    kill    BEGIN TRY KILL     CONVERT varchar 5   spid            END TRY BEGIN CATCH END CATCH    FROM master  sysprocesses  EXEC   kill

User · Answer

AlexK wrote a great answer  I just want to add my two cents  The code below is entirely based on  AlexK s answer  the difference is that you can specify the user and a time since the last batch was executed  note that the code uses sys dm exec sessions instead of master  sysprocess    DECLARE  kill varchar 8000   set  kill     select  kill    kill    kill      CONVERT varchar 5   session id        from sys dm exec sessions  where login name    usrDBTest  and datediff hh login time getdate     gt  1 --and session id in  311 266      exec  kill    In this example only the process of the user usrDBTest which the last batch was executed more than 1 hour ago will be killed

User · Answer

SELECT     spid      sp  status       loginame  Login       hostname       blocked BlkBy      sd name DBName       cmd Command      cpu CPUTime      memusage Memory      physical io DiskIO      lastwaittype LastWaitType       program name  ProgramName      last batch LastBatch      login time LoginTime       kill     CAST spid as varchar 10   as  Kill Command  FROM master dbo sysprocesses sp  JOIN master dbo sysdatabases sd ON sp dbid   sd dbid WHERE sd name NOT IN   master    model    msdb    --AND sd name    db name   --AND hostname like  hostname1    --AND loginame like  username1   ORDER BY spid     If a service connects continously  You can automatically execute kill process then run your script  DECLARE  sqlcommand nvarchar  500  SELECT  sqlcommand    kill     CAST spid as varchar 10   FROM master dbo sysprocesses sp  JOIN master dbo sysdatabases sd ON sp dbid   sd dbid WHERE sd name NOT IN   master    model    msdb    --AND sd name    db name   --AND hostname like  hostname1    --AND loginame like  username1   --SELECT  sqlcommand EXEC sp executesql  sqlcommand

User · Answer

You can get the script that SSMS provides by doing the following    Right-click on a database in SSMS and choose delete In the dialog  check the checkbox for  Close existing connections   Click the Script button at the top of the dialog    The script will look something like this   USE  master  GO ALTER DATABASE  YourDatabaseName  SET  SINGLE USER WITH ROLLBACK IMMEDIATE GO USE  master  GO DROP DATABASE  YourDatabaseName  GO

User · Answer

USE master GO ALTER DATABASE database name SET OFFLINE WITH ROLLBACK IMMEDIATE GO   Ref  http   msdn microsoft com en-us library bb522682 28v sql 105 29 aspx

User · Answer

To my experience  using SINGLE USER helps most of the times  however  one should be careful  I have experienced occasions in which between the time I start the SINGLE USER command and the time it is finished    apparently another  user  had gotten the SINGLE USER access  not me  If that happens  you re in for a tough job trying to get the access to the database back  in my case  it was a specific service running for a software with SQL databases that got hold of the SINGLE USER access before I did   What I think should be the most reliable way  can t vouch for it  but it is what I will test in the days to come   is actually  - stop services that may interfere with your access  if there are any  - use the  kill  script above to close all connections - set the database to single user immediately after that - then do the restore

User · Answer

This solution worked for me  DECLARE  kill varchar 8000          SELECT  kill    kill    kill     CONVERT varchar 5   session id          FROM sys dm exec sessions WHERE database id    db id  MyDB    EXEC  kill

[sql] Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to sql-server-2008-r2