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

Ho un database di sviluppo che viene ridistribuito frequentemente da un progetto di database di Visual Studio (tramite una build automatica TFS).

A volte quando eseguo la mia build ottengo questo errore:

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. 

Ho provato questo:

 ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE 

ma non riesco ancora a eliminare il database. (La mia ipotesi è che la maggior parte degli sviluppatori abbia accesso a dbo ).

Posso eseguire manualmente SP_WHO e iniziare a uccidere le connessioni, ma ho bisogno di un modo automatico per farlo nella compilazione automatica. (Anche se questa volta la mia connessione è l’unica sul db che sto tentando di eliminare.)

C’è uno script in grado di abbandonare il mio database indipendentemente da chi è connesso?

aggiornato

Per MS SQL Server 2012 e versioni successive

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

Per 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); 
 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

È ansible 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 la tua finestra di query al solo accesso per fare come desideri).

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 momento in cui è finito … apparentemente un altro utente ha ottenuto 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 ha ottenuto l’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à: – interrompere i servizi che potrebbero 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 esegui il ripristino

Come ti sembra ?

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

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

Dovresti fare attenzione 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) 

@ Alex ha scritto un’ottima risposta . Voglio solo aggiungere i miei due centesimi. Il codice sottostante è interamente basato sulla risposta di @ AlexK, la differenza è che è ansible specificare l’utente e il tempo trascorso dall’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.

Puoi usare Cursor in questo modo:

 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 su questo nel mio blog qui: http://www.pigeonsql.com/single-post/2016/12/13/Kill-all-connections-on-DB-by-Cursor

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

Ho provato con successo con il semplice codice qui sotto

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