Como você expulsa os usuários de um banco de dados SQL Server 2008?

Eu sempre uso o seguinte:

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;

Às vezes, isso pode demorar um pouco e às vezes é bloqueado porque você está executando e tem uma conexão ativa com o banco de dados . Verifique se há outras janelas de consulta que possam ter o mesmo contexto de banco de dados - isso pode incluir caixas de diálogo abertas, Pesquisador de Objetos, IntelliSense, trabalhos de longa duração etc.

Quando termino de fazer minhas alterações na configuração desse banco de dados, simplesmente:

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

Embora, às vezes, o que preciso fazer com esse banco de dados exija que o banco de dados esteja online, então às vezes tenho que deixá-lo no modo de usuário único e fazer isso:

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

Agora posso fazer minhas alterações e, quando estiver pronto para que outros usuários se conectem, simplesmente:

ALTER DATABASE [dbname] SET MULTI_USER;

Existem duas maneiras de fazê-lo:

1. Clique com o botão direito do mouse no banco de dados no Pesquisador de Objetos, vá para Tarefas> Desanexar. Marque a caixa de seleção Soltar conexões.

2. Defina o banco de dados para o modo de usuário único conforme descrito aqui :

   -- 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
   

Normalmente, defino o banco de dados em single_user e, em seguida, aguardo atraso e, em seguida, defino o banco de dados de volta no multiusuário como abaixo:

-- 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'

Nenhuma das opções acima funcionou para mim porque o servidor foi atingido por várias tentativas de conexão remota.

Quando fechei a porta específica do banco de dados no firewall do Windows, o Alter .. Set Multi_User normal funcionou na primeira tentativa.

O seguinte realmente mata todas as conexões. Bastante útil nos casos em que a configuração do modo de usuário único falha

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)

Você pode usar o script abaixo para destruir todos ou modificar para um banco de dados específico.

Tudo o que pode ser morto, será! No entanto, os SPIDs do serviço SQL não serão afetados.

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

Eu uso este código:

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

Mas posso ver que o exemplo SINGLE USER é menos para digitar.