22

We need to do a restore, and cannot because other users are connected. We thought we had disconnected every process, but apparently not.

How can we, from Management Studio, kick off everyone else so we can do this backup?

25

There are two ways of doing it:

  1. Right click on the database in Object Explorer go to Tasks > Detach. Select the Drop Connections checkbox.

  2. Set the database to single-user mode as outlined here:

    -- hit Ctrl+Shift+M in SSMS to fill in the template parameter
    USE master;
    GO
    
    ALTER DATABASE N'<Database Name, sysname,>'
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    GO
    
    ALTER DATABASE N'<Database Name, sysname,>'
    SET READ_ONLY;
    GO
    
    ALTER DATABASE N'<Database Name, sysname,>'
    SET MULTI_USER;
    GO
    
  • Didn't realize I had so many comments lol. Marian is correct, no need to run the actual detach, just get the script to kill users. @NickChammas Yeah, making it read only prevents the users from reconnecting. When you set it multi-user you can do the restore. Also the db names are from a template, and as such the tags such as '<Database Name, sysname>' are intended to be replaced through use of Ctrl+Shift+M. In a non templated script, the dbnames would have no quotes around them. – Wil Jan 26 '17 at 19:59
42

I always use the following:

USE master; -- get out of dbname myself
GO
-- kick all other users out:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- prevent sessions from re-establishing connection:
ALTER DATABASE [dbname] SET OFFLINE;

Sometimes this may take a while, and sometimes it is blocked because you're the one running it, and you have an active connection to the database. Check for other query windows that might have the same database context - this can include open dialogs, Object Explorer, IntelliSense, long-running jobs, etc.

When I'm done making my changes to that database's config, I simply:

ALTER DATABASE [dbname] SET ONLINE;
ALTER DATABASE [dbname] SET MULTI_USER;

Though, sometimes, the thing I need to do to that database requires the database to be online, so sometimes I have to leave it in single-user mode and do this:

ALTER DATABASE [dbname] SET ONLINE;
GO
USE [dbname];

Now I can make my changes, and then when I'm ready for other users to connect, simply:

ALTER DATABASE [dbname] SET MULTI_USER;
2

I normally set database in single_user and then waitfor delay and then set the database back in multiuser as below :

-- to kill all connections for particular db ... otherwise the restore will fail as exclusive lock cannot be obtained for the db being restored.

    alter database db_name
    set single_user with rollback immediate
    waitfor delay '00:00:05'  -- wait for 5 secs
    alter database db_name
    set multi_user
    restore database db_name from disk = 'D:\restore\db_name.bak'
    with replace, stats = 10, recovery -- if you want to recover your database online
    -- optional if you dont have the same directory/file structure
    move 'datafile logical name' to 'E:\data\physical_name.mdf',
    move 'logfile logical name' to 'F:\log\physical_name_log.ldf'
  • Actually, it is not needed to "set multi_user", since you are running a single script (transaction) with the restore statement. The restore with recovery will take care of putting the database back in multi-user mode. – Svein Terje Gaup Jun 21 '16 at 13:28
1

None of the options above worked for me because the server got hammered by multiple remote connection attempts.

When i closed the specific database port on the windows firewall, the normal Alter .. Set Multi_User worked within the first attempt.

-1

The following actually kills all connections. Quite useful in cases where setting single user mode fails

declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'databasename'
set @execSql = '' 
select  @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from    master.dbo.sysprocesses
where   db_name(dbid) = @databaseName
     and
     DBID <> 0
     and
     spid <> @@spid
exec(@execSql)
  • 1
    This doesn't work because sysprocesses doesn't always account for all sessions that might hold locks in that database (think of the simple scenario where a query is run in the context of database A but joins a table in A and a table in B). – Aaron Bertrand Oct 8 '15 at 13:22
-1

You can use the script below to either nuke everyone, or modify for a specific DB.

Anything that can be killed off, will be! SQL service SPIDs however will not be affected.

Drop table #who

go 

Create table #who(  [spid] int,
                    [ECID] int,
                    [Status] varchar(100),
                    [Loginname] varchar(200),
                    [Hostname] varchar(200),
                    [blk] bit,
                    dbname varchar(200),
                    cmd varchar(1000),
                    requestID int
                  )

go

Insert into #who (Spid, ECID, Status, Loginname, hostname,blk, dbname, cmd, requestid)
exec sp_who

Declare cursKillUsers Cursor for Select 'Kill ' + cast(spid as varchar(100)) + ';' [SQL] from #who where dbname like '%'
Declare @sql varchar(200)
Open cursKillUsers
Fetch next from cursKillUsers into @sql
While @@fetch_status = 0 
begin

    print @sql
    Exec (@sql)
    Fetch next from cursKillUsers into @sql

end

close cursKillUsers
deallocate cursKillUsers
-3

I use this code:

ALTER DATABASE [Dbname] set offline with rollback immediate
GO
ALTER DATABASE [Dbname] set online
GO

But I can see the SINGLE USER example is less to type.

  • 3
    Also setting the database back online means other users can connect again before you start your restore. – Aaron Bertrand Jun 2 '12 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.