Script per uccidere tutte le connessioni a un database (più di RESTRICTED_USER ROLLBACK)

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Rif: http://msdn.Microsoft.com/en-us/library/bb522682%28v=sql.105%29.aspx

È possibile ottenere lo script fornito da SSMS effettuando le seguenti operazioni:

1. Fare clic con il tasto destro del mouse su un database in SSMS e selezionare Elimina
2. Nella finestra di dialogo, seleziona la casella di controllo "Chiudi connessioni esistenti".
3. Fai clic sul pulsante Script nella parte superiore della finestra di dialogo.

Lo script sarà simile a questo:

USE [master]
GO
ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
DROP DATABASE [YourDatabaseName]
GO

Poco noto: l'istruzione GO sql può prendere un numero intero per il numero di volte in cui ripetere il comando precedente.

Quindi se tu:

ALTER DATABASE [DATABASENAME] SET SINGLE_USER
GO

Poi:

USE [DATABASENAME]
GO 2000

Ciò ripeterà il comando USE 2000 volte, imporrà deadlock su tutte le altre connessioni e diventerà proprietario della singola connessione. (Dare alla tua finestra di query l'accesso esclusivo per fare come desideri.)

Lo script estremamente efficiente di Matthew è stato aggiornato per utilizzare DMV dm_exec_sessions, sostituendo la tabella di sistema sysprocesses deprecata:

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('<YourDB>');

EXEC sys.sp_executesql @Kill;

Alternativa con il ciclo WHILE (se si desidera elaborare qualsiasi altra operazione per esecuzione):

USE [master];
GO

DECLARE @DatabaseID SMALLINT = DB_ID(N'<YourDB>');    
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;

Secondo la mia esperienza, l'uso di SINGLE_USER aiuta la maggior parte delle volte, tuttavia, si dovrebbe fare attenzione: ho vissuto occasioni in cui tra il momento in cui avvio il comando SINGLE_USER e il tempo in cui è finito ... apparentemente un altro utente ha ottenuto il Accesso SINGLE_USER, non io. Se ciò accade, ti stai impegnando a fondo per cercare di ottenere l'accesso al database (nel mio caso si trattava di un servizio specifico in esecuzione per un software con database SQL che si impossessava dell'accesso SINGLE_USER prima di me). Quello che penso dovrebbe essere il modo più affidabile (non posso garantire per questo, ma è quello che proverò nei giorni a venire), in realtà è:
- interrompe i servizi che possono interferire con il tuo accesso (se ce ne sono)
- usa lo script 'kill' sopra per chiudere tutte le connessioni
- imposta subito il database su single_user
- quindi eseguire il ripristino

Puoi usare Cursore come quello:

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

Ho scritto a riguardo nel mio blog qui: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by- Cursore

Dovresti stare attento alle eccezioni durante i processi di uccisione. Quindi puoi usare questo 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)

La risposta accettata ha lo svantaggio di non considerare che un database può essere bloccato da una connessione che sta eseguendo una query che coinvolge le tabelle in un database diverso da quello connesso a.

Questo può essere il caso se l'istanza del server ha più di un database e la query direttamente o indirettamente (ad esempio tramite sinonimi) usa tabelle in più di un database, ecc.

Trovo quindi che a volte è meglio usare syslockinfo per trovare le connessioni da uccidere.

Il mio suggerimento sarebbe quindi di utilizzare la seguente variazione della risposta accettata da 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);

@ Alex ha scritto una grande risposta . Voglio solo aggiungere i miei due centesimi. Il codice seguente è interamente basato sulla risposta di @ AlexK, la differenza è che è possibile specificare l'utente e un tempo dall'esecuzione dell'ultimo batch (si noti che il codice utilizza sys.dm_exec_sessions invece di 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()) > 1
--and session_id in (311,266)    
exec(@kill)

In questo esempio, verrà ucciso solo il processo dell'utente usrDBTest che l'ultimo batch è stato eseguito più di 1 ora fa.

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