SqlServer - Delete en masse

Tous les records effacés par l'instruction SQL Delete sont loggées dans le transaction log.
Lors de l'effacement d'enregistrements en masse dans de grandes tables, cette opération peut rapidement saturer le Transaction Log (et même le disque).
Lorsque la table doit être entièrement vidée, l'utilisation de TRUNCATE TABLE vient à point.
Cependant, pour les opérations d'archivage partiel (ou certains records doivent rester présents dans la table) ce n'est pas aussi simple.
Heureusement, il existe une solution.

Etape 1: 
Modifier la Configuration de la DB et activer l'option "Truncate Log On Checkpoint".
Lorsque la DB est configurée en "Truncate Log On Checkpoint" (condition nécessaire dans notre cas), le transaction Log sera automatiquement tronqué à chaque checkpoint effectué par la DB.
Il est possible d'effectuer des effacements en masse en utilisant de multiples petites transactions sans saturer le transaction Log.

Etape 2: 
Utiliser l'une des méthodes suivantes:

Méthode 1

Utiliser des batch... utilisant SET ROWCOUNT pour limiter le nombre d'enregistrements effacés par passe.
L'utilisation de multiples passes permet à SQL server d'éventuellement placer un checkpoint. Dans le cas contraire, tous les enregistrements seraient poussés en une seule fois dans la transaction log (aie-aie-aie!!).

WHILE EXISTS ( SELECT * FROM table WHERE condition_to_delete )

BEGIN

SET ROWCOUNT 1000

DELETE Table WHERE condition_to_delete

SET ROWCOUNT 0

END

Commentaire:
Durant mes essais, je n'ai pas eu l'occasion de voir SQL server placer efficacement un checkpoint.
Mon transaction log devenait suffisament grand (> 200Mb) pour que j'arrête moi même l'exécution du batch.
Bref, bien que le SQL et le principe semblent correct, la méthode ne m'a pas convaincu!


Méthode 2:
Dans ce cas de figure les modifications sont régulièrement commitées dans la DB. Par conséquent toute option de rollback est impossible... soyez certains de ce que vous faite!

set rowcount 5000

while 1=1

begin

begin tran

delete from X

commit

if @@rowcount = 0

break

end

set rowcount 0

Méthode recommandée:
J'ai personnelement utilisé la variation suivante (mixant la méthode 1 et 2) m'ayant permis d'éliminer 4 Go de donnée avec un transation log n'ayant pas dépassé 80 Mo.
C'est par conséquent la méthode que je recommande.

WHILE EXISTS ( SELECT * FROM tblXmlMsg where ID_Session < 4356)

BEGIN

print '.'

begin transaction

SET ROWCOUNT 300

DELETE tblXmlMsg where ID_Session < 4356

Commit transaction

SET ROWCOUNT 0

CHECKPOINT

END 

20/05/2009 - mise à jour - Une méthode encore bien meilleure car elle évite le "Select" avant l'effacement des enregistrement. Dans une table de 40 millions de records, cela fait une différence vraiment impressionnante.

DECLARE @iTotal INT
SELECT @iTotal = 0 

DECLARE @iRowCount INT
SELECT @iRowCount = 1
WHILE @iRowCount > 0
BEGIN
  SET ROWCOUNT 1000
  DELETE tblSSAudit WHERE 
    ID_Audit > 22726409 and ID_Audit <= 45799397 and 
    ID_ObjectType = 78 and ID_Person = 7818 
  SELECT @iRowCount = @@RowCount
  CHECKPOINT
  SET ROWCOUNT 0
  SELECT @iTotal = @iTotal + @iRowCount
  print Cast( @iTotal as VarChar(10) )+' records deleted'
END

Etape 3: 
Ne pas oublier de restaurer les options d'origine de la DB.
Faire un full backup et non un Backup LOG parce que le contenu du transaction log n'est plus "continu" (ce qui empêchera une tâche de restauration complète).

source: Cet article sur SQL Server Forums