Wie man Datensätze in SQL 2005 löscht und dabei die Transaktionsprotokolle unter Kontrolle hält

Question

Ich führe die folgende gespeicherte Prozedur aus, um eine große Anzahl von Datensätzen zu löschen. Ich weiß, dass die DELETE-Anweisung in das Transaktionsprotokoll schreibt und das Löschen vieler Zeilen das Protokoll wachsen lässt.

Ich habe andere Optionen zum Erstellen von Tabellen und Einfügen von Datensätzen zu halten und dann Truncating die Quelle, wird diese Methode nicht für mich arbeiten sah.

Wie kann ich meine gespeicherte Prozedur unten effizienter gestalten und gleichzeitig sicherstellen, dass das Transaktionsprotokoll nicht unnötig wächst?

CREATE PROCEDURE [dbo].[ClearLog] 
(
  @Age int = 30
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

  -- DELETE ERRORLOG
  WHILE EXISTS ( SELECT [LogId]  FROM [dbo].[Error_Log] WHERE DATEDIFF( dd, [TimeStamp], GETDATE() ) > @Age )
   BEGIN
    SET ROWCOUNT 10000
    DELETE [dbo].[Error_Log] WHERE DATEDIFF( dd, [TimeStamp], GETDATE() ) > @Age

    WAITFOR DELAY '00:00:01'
    SET ROWCOUNT 0
   END
END

Answer 1

So würde ich es machen:

CREATE PROCEDURE [dbo].[ClearLog] (  
@Age int = 30)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @d DATETIME
        , @batch INT;
    SET @batch = 10000;
    SET @d = DATEADD( dd, -@Age, GETDATE() )
    WHILE (1=1)
    BEGIN
        DELETE TOP (@batch) [dbo].[Error_Log]  
        WHERE [Timestamp] < @d;
        IF (0 = @@ROWCOUNT)
            BREAK
    END
END

• Machen Sie den Tiemstamp-Vergleich SARGable
• Trennen Sie die Funktion GETDATE() am Anfang des Stapels, um einen konsistenten Lauf zu gewährleisten (andernfalls kann es zu einer Endlosschleife kommen, da neue Datensätze "altern", während die alten gelöscht werden).
• TOP anstelle von SET ROWCOUNT verwenden ( Abgelehnt : Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. )
• prüfen Sie @@ROWCOUNT, um die Schleife zu unterbrechen, anstatt redundante SELECT

Answer 2

Angenommen, Sie haben die Möglichkeit, die Fehlerprotokolltabelle auf einem Partitionsschema neu zu erstellen, wäre eine Option, die Tabelle nach Datum zu partitionieren und die Partitionen auszutauschen. Führen Sie eine Google-Suche nach "alter table switch partition" durch, um ein wenig weiter zu graben.

Answer 3

Wie wäre es, wenn Sie es öfter ausführen und dabei jedes Mal weniger Zeilen löschen? Führen Sie dies alle 30 Minuten durch:

CREATE PROCEDURE [dbo].[ClearLog] 
(
  @Age int = 30
)
AS
BEGIN
    SET NOCOUNT ON;
    SET ROWCOUNT 10000 --I assume you are on an old version of SQL Server and can't use TOP
    DELETE dbo.Error_Log Where Timestamp>GETDATE()-@Age
    WAITFOR DELAY '00:00:01' --why???
    SET ROWCOUNT 0
END

die Art und Weise, wie die Daten verarbeitet werden, wird die Zeit nicht abgeschnitten, und Sie werden jedes Mal nur Daten im Wert von 30 Minuten löschen.

Answer 4

Wenn sich Ihre Datenbank im FULL-Recovery-Modus befindet, besteht die einzige Möglichkeit, die Auswirkungen Ihrer Löschanweisungen zu minimieren, darin, sie "zu verteilen", d.h. nur so viele Zeilen in einem "Transaktionsintervall" zu löschen. Wenn Sie zum Beispiel stündlich t-log-Backups durchführen, sollten Sie nur 20.000 Zeilen pro Stunde löschen. Das mag nicht alles sein, was Sie auf einmal brauchen, aber wird sich das nach 24 Stunden oder nach einer Woche ausgleichen?

Wenn sich Ihre Datenbank im SIMPLE- oder BULK_LOGGED-Modus befindet, sollte es genügen, die Löschvorgänge in Stücke zu unterteilen. Da Sie das aber bereits tun, muss ich davon ausgehen, dass sich Ihre Datenbank im FULL recover mode befindet. (Das, oder die Verbindung, die die Prozedur aufruft, könnte Teil einer Transaktion sein.)

Answer 5

Eine Lösung, die ich in der Vergangenheit verwendet habe, bestand darin, das Wiederherstellungsmodell vorübergehend auf "Bulk Logged" und am Ende der gespeicherten Prozedur wieder auf "Full" zu setzen:

DECLARE @dbName NVARCHAR(128);
SELECT @dbName = DB_NAME();

EXEC('ALTER DATABASE ' + @dbName + ' SET RECOVERY BULK_LOGGED')

WHILE EXISTS (...)
BEGIN
    -- Delete a batch of rows, then WAITFOR here
END

EXEC('ALTER DATABASE ' + @dbName + ' SET RECOVERY FULL')

Dadurch wird der Verbrauch des Transaktionsprotokolls bei großen Stapeln erheblich reduziert. Mir gefällt nicht, dass das Wiederherstellungsmodell für die gesamte Datenbank festgelegt wird (nicht nur für diese Sitzung), aber es ist die beste Lösung, die ich finden konnte.