Procédure stockée pour supprimer toutes les connexions à la base de données ou au serveur sélectionné

Procédure stockée SQL pour supprimer toutes les connexions à la base de données ou au serveur sélectionné, à l'exception du processus qui l'appelle.

introduction

Il peut arriver que vous deviez déconnecter tous les utilisateurs de votre base de données, par exemple pour la réplication Log Shipping. Ce code ci-dessous a été utilisé sur un serveur de reporting mis à jour toutes les 20 minutes, car la base de données doit être ouverte en mode exclusif par la tâche de restauration.

Le code ci-dessous acceptera un nom de base de données, il ne doit donc être écrit qu'une seule fois et stocké sur la base de données principale ou des utilitaires (si vous en avez créé un). Il peut alors être appelé depuis n'importe quel autre processus, dans notre cas le plan de maintenance complet dans le document ci-dessus.

Il s'agit d'une procédure stockée assez simple qui obtient simplement une liste des connexions actives et des boucles à travers chacune, tuant le processus et la connexion.

SQL Code

CREATE PROC maint.KillConnections(@database VARCHAR(50)) AS BEGIN

SET NOCOUNT ON;

DECLARE @spid INT

DECLARE @killstatement NVARCHAR(10)--Declare a cursor to select the users connected to the specified database

DECLARE c1 CURSOR FAST_FORWARD FOR SELECT request_session_id                    


FROM sys.dm_tran_locks 


WHERE resource_type='DATABASE' 


AND (DB_NAME(resource_database_id)=@database OR @database IS NULL)

OPEN c1

FETCH c1 INTO @spid

WHILE @@FETCH_STATUS= 0 BEGIN

      IF @@SPID<>@spid--Don't kill the connection of the user executing this statement

      BEGIN

            -- Construct dynamic sql to kill spid

            SET @killstatement ='KILL '+CAST(@spid AS VARCHAR(5))

            EXEC sp_executesql @killstatement

            PRINT @spid -- Print killed spid            


      END

      FETCH NEXT FROM c1 INTO @spid

END

-- Clean up

CLOSE c1

DEALLOCATE c1

END

GO