Script per uccidere tutte le connessioni ad un database (Più di RESTRICTED_USER ROLLBACK)

Ho un database di sviluppo che la re-distribuzione di frequenza di un Database di Visual Studio progetto (attraverso un TFS creazione Automatica).

A volte, quando ho eseguito la mia generazione 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 ancora non riesco a eliminare il database. (La mia ipotesi è che la maggior parte degli sviluppatori hanno dbo di accesso.)

Posso eseguire manualmente SP_WHO e iniziare a uccidere i collegamenti, ma ho bisogno di un modo automatico per fare questo in automatico costruire. (Anche se questa volta la mia connessione è l’unico sul db sto cercando di drop.)

C’è uno script che può cadere il mio database, indipendentemente da chi è connesso?

InformationsquelleAutor Vaccano | 2011-08-25



12 Replies
  1. 562

    Aggiornato

    Per MS SQL Server 2012 e al di sopra di

    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); 
    • Questa è la migliore risposta dei due; evita di prendere il database non in linea, e ha accettato di rispondere non sempre funziona (a volte non può arrotolare il tutto indietro).
    • Una bella risposta per l’aggregazione di kill dichiarazioni tutti insieme. Vorrei utilizzare un cursore per uccidere ogni processo, che ovviamente non è efficiente a tutti. La tecnica utilizzata in questa risposta, è brillante.
    • Sono d’accordo con Marco. Questo metodo deve essere accettata la risposta è molto più elegante e meno impattanti per il database.
    • buona e veloce. Unico problema potrebbe essere il sistema spid così u può aggiungere DOVE dbid = db_id(‘My_db’) e spid > 50
    • Come si fa a risolvere il “problema” non si può uccidere il vostro processo di’ ? Devo aprire una finestra di query per eseguire una nuova query. Quindi credo che questa e ‘la mia proprio processo’, la mia connessione al database.
    • È necessario modificare il contesto del database prima di eseguire lo script. Es.: USE [Master]
    • vedere sqlblog.com/blogs/adam_machanic/archive/2010/06/23/…spid>50 non il migliore
    • Questo era esattamente ciò di cui avevo bisogno, questa è la risposta migliore a mio parere
    • Ho usato questo codice esattamente, e ottengo l’errore: “sintassi non corretta in prossimità di ‘Andare’.” Qualcuno sa perché questo sarebbe accaduto?
    • Il database_id colonna è non disponibile in SQL Server 2008. E ‘ disponibile solo da SQL Server 2012 in poi. Vedere MSDN per riferimento.
    • SQL 2000/2005 script non funziona, come non è possibile assegnare un valore a una variabile al momento della dichiarazione (che la funzionalità è stata introdotta nel 2008). Modificare la prima riga di DECLARE @kill varchar(8000) e l’aggiunta di SET @kill = '' opere
    • Risolto, grazie @Adwaenyth e DeanOC
    • Aggiungere E [email protected]@SPID per la clausola WHERE non e ‘Uccidere’ di te…
    • Ho dovuto riavviare tutti i miei Domini
    • Aggregazione di concatenazione di stringhe (@kill = @kill + …) non è supportato e può produrre risultati imprevisti. Sarebbe meglio per il refactoring utilizzando FOR XML PATH o, in SQL 2017 e successivi. STRING_AGG.
    • Ho provato in questo modo e ho un “utente Solo, i processi possono essere uccisi.” messaggio di errore. cosa devo fare?
    • Puoi provare a DOVE dbid = db_id(‘My_db’) e spid > 50
    • Voglio uccidere tutte le connessioni tranne la mia. Aggiunto and session_id != @@SPID per la clausola where.

  2. 124
    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

    • Stranamente è stato USE master che è stata la chiave. Stavo cercando di far cadere il db, mentre ad esso collegati (Duh!). Grazie!
    • Se si utilizza SET OFFLINE è necessario eliminare manualmente il file db.
    • Non alter database YourDatabaseName set SINGLE_USER with rollback immediate essere di meglio? Se si imposta OFFLINE (come @mattalxndr stati) il file verrà lasciato sul disco, ma con SINGLE_USER la connessione verrà lasciato da solo, e drop database YourDatabaseName ancora rimuovere il file.
    • nello script, non si è collegati al DB, quindi non è “connessione”, ma alcuni altri che verranno a sinistra. Subito dopo il set offline, è possibile emettere set online per evitare i residui di file problema (sì, c’è una gara condizione di possibilità).
    • Grazie! Non mi rendo conto che una scheda con l’istruzione sql in SQL Management Studio, eseguito in precedenza su questo database, è stata la causa del mio db di essere segnalata in uso. Utilizzare master, va fatto tutto il lavoro!
    • In realtà, anche in modalità utente singolo è bacato (che è davvero l’unica parola): garantisce nessun altro “normale”, gli utenti possono connettersi, ma built-in di processi come quelli per l’auto-aggiornamento asincrono statistiche sono non disabili, e quando sono in esecuzione, essi ti dirottare l’unica connessione, l’uccisione del suo processo. Se ci sono qualsiasi asincrona processi in esecuzione, non vi è alcun modo affidabile per applicare modalità utente singolo, ahimè – ma metti offline non di lavoro. Ho imparato questo nel modo più duro sulla nostra CI, che ha usato la modalità utente singolo regolarmente come parte di schema prove di migrazione.

  3. 24

    Si può ottenere lo script sql server management studio fornisce effettuando le seguenti operazioni:

    1. Fare clic destro su di un database in sql server management studio e scegliere elimina
    2. Nella finestra di dialogo, selezionare la casella di controllo per “Chiudere le connessioni esistenti.”
    3. Fare clic sul pulsante Script in cima alla finestra di dialogo.

    Lo script sarà simile a questa:

    USE [master]
    GO
    ALTER DATABASE [YourDatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    USE [master]
    GO
    DROP DATABASE [YourDatabaseName]
    GO
    • Io non consiglia l’assunzione di base dati in modalità utente singolo per qualsiasi di utenti come questo può causare sciolto connessione corrente per alcune applicazioni utente e unnecesarry difficoltà di trovare gli utenti e uccidere stessa o alcune volte u necessario riavviare sql server se le connessioni al db sono così frequenti.
  4. 6

    Poco conosciuto: il GO istruzione sql può prendere un numero intero per il numero di volte per ripetere il comando precedente.

    Quindi, se si:

    ALTER DATABASE [DATABASENAME] SET SINGLE_USER
    GO

    Quindi:

    USE [DATABASENAME]
    GO 2000

    Questo ripetere il comando USE 2000 volte, la forza di deadlock su tutte le altre connessioni, e assumere la proprietà della singola connessione. (Dando la vostra finestra di query di accesso unico a fare come vuoi.)

    • GO non è un TSQL comando, ma un comando speciale solo riconosciuto dal sqlcmd e l’utilità osql e sql server management studio.
  5. 3

    Matteo estremamente efficiente script aggiornati per utilizzare il dm_exec_sessions DMV, sostituendo il vecchio sistema 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('<YourDB>');
    
    EXEC sys.sp_executesql @Kill;

    In alternativa con ciclo WHILE (se si desidera elaborare eventuali altre operazioni per l’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;
  6. 3

    Per la mia esperienza, utilizzando SINGLE_USER aiuta la maggior parte delle volte, tuttavia, si dovrebbe essere attento: io ho vissuto momenti in cui tra il momento di avviare la SINGLE_USER comando e il tempo è finito… a quanto pare un altro “utente”, aveva ottenuto il SINGLE_USER di accesso, non a me. Se ciò accade, sarà un lavoro duro, cercando di ottenere l’accesso al database (nel mio caso, è stato un servizio specifico per l’esecuzione di un software con database SQL, che entrato in possesso del SINGLE_USER di accesso prima di me).
    Quello che penso dovrebbe essere il modo più affidabile (non è possibile garantire per esso, ma è quello che mi metterà alla prova nei giorni a venire), è in realtà:

    – stop servizi che possono interferire con l’accesso (se ci sono)

    – utilizzare il ‘kill’ script sopra per chiudere tutte le connessioni

    – impostare il database single_user subito dopo

    – poi fare il ripristino

    • Se il SINGLE_USER comando è nella stessa partita come il tuo (script) comando di ripristino — non separati da una istruzione GO! — allora, nessun altro processo può afferrare l’accesso per singolo utente, nella mia esperienza. Tuttavia, mi è stato catturato questa sera, perché il mio lavoro programmato notturno di set-singolo-utente;ripristino;set-multi-utente è fatto esplodere. un altro processo esclusivo di accesso ai file per il mio file bak (smh), e quindi il ripristino non riuscito, seguito dal MULTI_USER in mancanza di significato … quando mi ha chiamato nel cuore della notte per ripulire il sangue, qualcun altro ha avuto SINGLE_USER accesso e doveva essere ucciso.
  7. 1

    Si dovrebbe essere attenti circa le eccezioni durante l’interruzione di processi. Così si può utilizzare 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)
  8. 1

    @AlexK ha scritto un grande risposta. Voglio solo aggiungere i miei due centesimi. Il codice seguente è interamente basato su @AlexK risposta, la differenza è che è possibile specificare utente e una volta dato l’ultimo lotto è stato eseguito (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, solo il processo dell’utente usrDBTest cui l’ultimo lotto è stato eseguito più di 1 ora fa, sarà ucciso.

  9. 1

    È possibile utilizzare Cursore così:

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

  10. 1

    Accettato di rispondere ha l’inconveniente di non prendere in considerazione che un database può essere bloccato da una connessione che è l’esecuzione di una query che coinvolge tabelle in un database diverso da quello connesso.

    Questo può essere il caso se l’istanza del server dispone di più di un database e le query direttamente o indirettamente (ad esempio attraverso sinonimi) usare le tabelle in più di un database etc.

    Quindi, io trovo che a volte è meglio usare syslockinfo di trovare i collegamenti per uccidere.

    Il mio consiglio sarebbe quindi di utilizzare il seguente variazione del accettati risposta 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);
  11. 0
    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
    */
  12. -1

    Ho testato con successo con semplice codice riportato di seguito

    USE [master]
    GO
    ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    • Io ho avuto problemi con set SINGLE_USER quando c’era già una singola connessione attiva.

Lascia un commento