Reprendre la main sur une base de données

2 réponses

Beaucoup plus simple qu’il n’y parait, mais attention. Ne tombez pas dans le piège à c…

Dans ce post :
http://www.developpez.net/forums/showthread.php?t=92217
Un utilisateur demande :
Comment est-il possible d’éjecter tous les utilisateurs d’une base avant la restauration d’une autre version de celle-ci ?

Et un internaute de lui répondre par le code suivant :

CREATE PROCEDURE usp_KillUsers @dbname varchar(50) AS
DECLARE @strSQL varchar(255)
 
CREATE TABLE #tmpUsers (
spid int,
eid int,
STATUS varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30))
 
INSERT INTO #tmpUsers EXEC SP_WHO
 
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname
 
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor
 
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
   IF (@@fetch_status <> -2)
   BEGIN
      SET @strSQL = 'KILL ' + @spid
      EXEC (@strSQL)
   END
   FETCH NEXT FROM LoginCursor INTO  @spid, @dbname2
END
 
CLOSE LoginCursor
DEALLOCATE LoginCursor
 
DROP TABLE #tmpUsers
go

Or à l’évidence ce traitement n’offre acune assurance de bonne fin….

En effet, l’opération KILL lance un ROLLBACK d’autorité sur la connexion en cours. Or un ROLLBACK est une opération couteuse en traitement donc en temps. Or notre quidam à pris une « photo » des connexions en cours à l’aide d’un curseur. Le temps que tous les KILL s’effectuent, et vous pouvez être sûr qu’un utilisateur tentera de se reconnecter, en particulier lorsque la base de données est associée à un site web fort sollicité… Tant est si bien que jamais cette opération ne produira le résultat escompté !

Or il existe une commende spécifique pour traiter ce cas de figure. Elle consiste à placer la base de données dans un mode mono utilisateur, au profit de la connexion en cours (le vôtre) et à annuler d’autorité toutes les connexions en cours. Voici cette commande :

ALTER DATABASE <ma_base>
SET SIGNLE_USER  
    WITH ROLLBACK IMMEDIATE

Et le tour est joué !

Cette commande spécifiquement conçu a cet effet propose quelques variantes :
SET SINGLE_USER ou RESTRICTED_USER (membre de sysadmin)
WITH ROLLBACK AFTER n SECONDS ou ROLLBACK IMMEDIATE ou encore NO_WAIT

CONSEIL : n’utilisez jamais que cette commande

***
Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation

2 réflexions au sujet de « Reprendre la main sur une base de données »

  2. Avatar de alassanediakitealassanediakite

    Salut et encore mille merci.
    Pourquoi « CONSEIL : n’utilisez jamais que cette commande »? En fait, je ne sais pas si c’est pour
    « ALTER DATABASE
    SET SIGNLE_USER
    WITH ROLLBACK IMMEDIATE »
    ou pour
    « SET SINGLE_USER ou RESTRICTED_USER (membre de sysadmin)
    WITH ROLLBACK AFTER n SECONDS ou ROLLBACK IMMEDIATE ou encore NO_WAIT »
    ou pour les deux.

    ↓

Laisser un commentaire