How to close all active database connections in one shot?

While trying to restore SQL Server database or do any other actions which require exclusive database access you may face with the following error:

Exclusive access could not be obtained because the database is in use.

Here is a quick solution which is much handier than killing all connections manually from SSMS GUI. Before running the script below make sure that you`re not killing the connections of production users for example or affecting business in any other unwanted way. And don`t forget to put the name of your database to the script.

-- USE [master] GO DECLARE @dbname nvarchar(255) DECLARE @spid int DECLARE @command nvarchar(300) /*------------------------------------- -------Put your database name here ---- --------------------------------------*/ SET @dbname = 'Your database name here' /*------------------------------------*/ DECLARE dbprocess CURSOR FAST_FORWARD FOR SELECT sys.databases.name, sys.sysprocesses.spid FROM sys.sysprocesses INNER JOIN
sys.databases ON sys.sysprocesses.dbid = sys.databases.database_id WHERE (sys.databases.name = @dbname) OPEN dbprocess
FETCH NEXT FROM dbprocess INTO @dbname, @spid WHILE @@FETCH_STATUS = 0 BEGIN SET @command = ' ' SET @command = 'Kill' +' '+ cast(@spid as nvarchar(5)) EXEC (@command) FETCH NEXT FROM dbprocess INTO @dbname, @spid END CLOSE dbprocess DEALLOCATE dbprocess

After running the script above you can proceed with the operation which required exclusive access to the database and now it will be successful. 

 

acyclovir cheap onlinebuy generic acyclovir cream onlineaciclovir cream buy online ukwhere can i buy acyclovir cream onlineaciclovir tablets buy online australiaaciclovir tablets 800 mg buy onlinebuy aciclovir tablets online ukbuy acyclovir cream for genital herpeswhere can i buy acyclovir creambuy acyclovir cream 5
  • Jim Nichols says:

    Or you could simply run the command:
    Alter database [DbName] set single_User WITH ROLLBACK IMMEDIATE
    Unless I am missing something on why you would write all that code to do the same thing.

  • Paul says:

    Here’s an alternate method that doesn’t require a cursor.

    ALTER DATABASE MyDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
    ALTER DATABASE MyDatabaseName SET ONLINE

  • Purchase acyclovir cream, Can you buy aciclovir tablets