Frage Wie reduziere ich die Größe des Transaktionslog-Backups nach einer vollständigen Sicherung?


Ich habe drei Wartungspläne eingerichtet, die auf einer Sql Server 2005-Instanz ausgeführt werden sollen:

  • Wöchentliche Datenbankoptimierungen gefolgt von einer vollständigen Sicherung
  • Tägliche differenzielle Sicherung
  • Stündliche Transaktionsprotokollsicherungen

Die stündlichen Log-Backups liegen in der Regel zwischen einigen hundert Kb und 10 Mb, abhängig vom Aktivitätslevel. Die täglichen Unterschiede steigen am Ende der Woche auf etwa 250 Mb und das wöchentliche Backup liegt bei 3,5 Gb.

Das Problem, das ich habe, ist, dass die Optimierungen vor der vollständigen Sicherung dazu führen, dass die nächste Transaktionsprotokollsicherung auf mehr als die doppelte Größe der vollständigen Sicherung, in diesem Fall 8 GB, wächst, bevor sie wieder normal wird.

Außer BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLYGibt es eine Möglichkeit, die Größe dieser Protokollsicherung zu reduzieren oder zu verhindern, dass die Optimierungen überhaupt im Transaktionsprotokoll aufgezeichnet werden, da sie sicherlich in der vollständigen Sicherung berücksichtigt werden, der sie vorangehen?


37
2018-05-27 12:23


Ursprung


+1 Dies wurde aufgrund des Gedankenaustausches, der durch diese Frage hervorgerufen wurde, aufgewertet. - MarlonRibunal


Antworten:


Einige interessante Vorschläge hier, die alle ein Missverständnis darüber zu geben scheinen, wie Log-Backups funktionieren. Eine Protokollsicherung enthält das ALL-Transaktionsprotokoll, das seit der letzten Protokollsicherung generiert wurde, unabhängig davon, welche vollständigen oder differenziellen Sicherungen zwischenzeitlich erstellt wurden. Das Beenden von Protokollsicherungen oder das Verschieben auf tägliche vollständige Sicherungen hat keine Auswirkungen auf die Protokollsicherungsgrößen. Das einzige, was sich auf das Transaktionsprotokoll auswirkt, ist eine Protokollsicherung, sobald die Protokollsicherungskette gestartet wurde.

Die einzige Ausnahme von dieser Regel ist, wenn die Protokollsicherungskette unterbrochen wurde (z. B. durch Rückkehr zum SIMPLE-Wiederherstellungsmodell, Zurücksetzen von einem Datenbanksnapshot, Abschneiden des Protokolls mit BACKUP LOG WITH NO_LOG / TRUNCATE_ONLY). In diesem Fall die erste Protokollsicherung enthält das gesamte Transaktionsprotokoll seit der letzten vollständigen Sicherung, wodurch die Protokollsicherungskette neu gestartet wird; oder wenn die Protokollsicherungskette nicht gestartet wurde - wenn Sie zum ersten Mal in FULL wechseln, arbeiten Sie in einer Art Pseudo-SIMPLE-Wiederherstellungsmodell, bis die erste vollständige Sicherung ausgeführt wird.

Um Ihre ursprüngliche Frage zu beantworten, ohne in das SIMPLE-Wiederherstellungsmodell zu gehen, müssen Sie das gesamte Transaktionsprotokoll sichern. Abhängig von den Aktionen, die Sie ausführen, können Sie häufigere Protokollsicherungen durchführen, um ihre Größe zu reduzieren, oder eine zielgerichtetere Datenbank erstellen.

Wenn Sie einige Informationen über die Wartungsarbeiten, die Sie gerade machen, veröffentlichen können, kann ich Ihnen helfen, sie zu optimieren. Machen Sie zufälligerweise Index-Neuerstellungen, gefolgt von einer Verkleinerungs-Datenbank, um den von den Index-Neuerstellungen verwendeten Speicherplatz wiederzuerlangen?

Wenn Sie während der Wartung keine andere Aktivität in der Datenbank haben, können Sie Folgendes tun:

  • Stellen Sie sicher, dass die Benutzeraktivität gestoppt wurde
  • nehmen Sie eine abschließende Log-Sicherung vor (dadurch können Sie sich bis zum Beginn der Wartung erholen)
    • Wechseln Sie zum SIMPLE-Wiederherstellungsmodell
    • Wartung ausführen - das Protokoll wird an jedem Prüfpunkt abgeschnitten
    • Wechseln Sie zum Wiederherstellungsmodell VOLL und nehmen Sie eine vollständige Sicherung vor
    • fahre wie gewohnt fort

Hoffe das hilft - freue mich auf weitere Infos.

Vielen Dank

[Edit: Nach all der Diskussion darüber, ob eine vollständige Sicherung die Größe einer nachfolgenden Log-Sicherung ändern kann (kann es nicht), habe ich einen umfassenden Blogpost mit Hintergrundmaterial und einem Skript zusammengestellt, das es beweist. Schau es dir an http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-the-log-and-log-backups-how-to-convince-yourself.aspx]


30
2018-05-27 16:29



Paul - stimme überhaupt nicht zu. Protokollieren Sie keine Sicherungen während der Indexwartung. Das Protokoll wird erweitert, und die nächste Gesamtsicherung wird größer, aber Sie erhalten nicht die Leistungssteigerung der T-Protokoll-Sicherungen zur gleichen Zeit wie Ihre Indexwartungsaufträge. Kannst du den Verdienst davon sehen? Sicherlich würden Sie zustimmen, dass gleichzeitige T-Log-Sicherungen und Indexpflege einen Leistungseinbruch verursachen würden. - Brent Ozar
Nein - ich würde immer noch nicht zustimmen. Ich hätte lieber häufigere Protokollsicherungen, damit sie kleiner sind, als ein Monster, nachdem alle Wartungsarbeiten durchgeführt wurden. Log-Backups mit unverhältnismäßiger Größe können zu Problemen beim Kopieren über das Netzwerk führen (z. B. für Offsite-Backups oder Protokollversand). Wenn keine Benutzeraktivität besteht und die Protokollsicherungen nicht benötigt werden, dann könnte sein, aber wenn das System abstürzt und Sie ein Tail-of-the-Log-Backup durchführen müssen, wird das eine Menge Zeit in Anspruch nehmen, die Teil Ihrer Ausfallzeit ist. Ich sollte einen Blogeintrag darüber machen. - Paul Randal
Und selbst das hilft nicht der ursprünglichen Frage des OP, wie man die Größe des Log-Backups nach der Indexwartung reduzieren könnte - tatsächlich wird es möglicherweise größer werden, je nachdem, welche Operationen ausgeführt werden. - Paul Randal


Sie könnten sie verkleinern, aber sie wachsen einfach wieder an und verursachen schließlich eine Fragmentierung der Festplatte. Indexneuaufstellungen und -defrags machen sehr große Transaktionsprotokolle. Wenn Sie die Möglichkeit der punktuellen Wiederherstellung nicht benötigen, können Sie in den einfachen Wiederherstellungsmodus wechseln und die Transaktionsprotokollsicherungen vollständig entfernen.

Ich nehme an, dass Sie einen Wartungsplan für die Optimierungen verwenden. Sie könnten ihn ändern, um ein Skript zu verwenden, das nur dann Defragmente indexiert, wenn eine bestimmte Fragmentierungsstufe erreicht ist und Sie wahrscheinlich keinen Leistungseinbruch erleiden würden. Dies würde viel kleinere Protokolle erzeugen.

Ich würde täglich Unterschiede zugunsten der täglichen vollständigen Backups BTW überspringen.


4
2018-05-27 12:34



Ich nehme an, ich könnte am Ende des vollständigen Backups einfach ein TRUNCATE LOG machen, aber das scheint nicht die beste Methode zu sein, ich habe auf einige Alternativen gehofft ... Was wären die Vorteile von täglichen Backups? als Diffs? Das scheint nur mehr Platz für relativ wenig Nutzen zu nutzen. Ich kann auch nicht zur einfachen Wiederherstellung wechseln, da ich die Granularität der stündlichen Log-Sicherungen benötige. Schließlich bin ich mir nicht sicher, wie das Verschieben der Optimierungen in ein Skript helfen würde, sicherlich hätte ich das gleiche Problem noch seltener. - Dave
Ich habe das wegen des Vorschlags, Diffs zu überspringen und zu Tagesvollzählungen zu wechseln, abgelehnt. Warum? Fügt eine 3.5GB hinzu, während Diffs nur 250MB sind. Die Backup-Strategie sieht für mich absolut gut aus. Das Entfernen von Diffs bedeutet, dass viele GBs mehr Speicherplatz für nur eine kleine, winzige Beschleunigung in der Wiederherstellungszeit haben. - Paul Randal
Die Situation ist anders, es gibt nichts falsches mit diffs, aber wenn Platz nicht so wichtig ist, wenn Sie sich schnell erholen müssen, ist es besser, einen Schritt als zwei zu haben. - SqlACID
@Dave Siehe Jeremys Antwort, erstellen Sie eine gespeicherte Prozedur, um bestimmte Dateien zu defragmentieren, teilen Sie sie in kleinere Teile auf. - SqlACID


Ihre letzte Frage war: "Gibt es außer BACKUP LOG WITH TRUNCATE_ONLY eine Möglichkeit, die Größe dieser Log-Sicherung zu reduzieren oder zu verhindern, dass die Optimierungen überhaupt im Transaktionsprotokoll aufgezeichnet werden, da sie sicherlich in der vollständigen Sicherung berücksichtigt werden, der sie vorausgehen?"

Nein, aber hier ist ein Workaround. Wenn Sie wissen, dass zu diesem Zeitpunkt die einzigen Aktivitäten in dieser Datenbank die Indexwartungsjobs sind, können Sie die Transaktionsprotokollsicherungen stoppen, bevor die Indexwartung beginnt. Zum Beispiel, einige meiner Server am Samstagabend, die Jobpläne sehen so aus:

  • 21.30 Uhr - Transaktionsprotokollsicherung wird ausgeführt.
  • 21:45 - Die Transaktionsprotokollsicherung wird zum letzten Mal ausgeführt. Der Zeitplan endet um 9:59 Uhr.
  • 22:00 Uhr - Der Indexwartungsjob startet und hat integrierte Stopps, die vor 11:30 enden.
  • 23:30 Uhr - Der vollständige Backup-Job startet und endet in weniger als 30 Minuten.
  • 12:00 Uhr - Transaktionsprotokollsicherungen beginnen alle 15 Minuten erneut.

Das bedeutet, dass ich zwischen 9:45 und 23:30 Uhr keine Point-in-Time-Wiederherstellbarkeit habe, aber die Auszahlung ist eine schnellere Leistung.


2
2018-05-27 14:23



Und Sie müssen kurz vor 22 Uhr zu SIMPLE wechseln, oder? Andernfalls enthält die 12-Uhr-Protokollsicherung das gesamte zwischen 10 Uhr und 12 Uhr generierte Protokoll. - Paul Randal
Oops vergaß zu erwähnen, dass ich das auch abgelehnt habe, weil du nicht erwähnt hast, dass du in SIMPLE bist. Wenn Sie in BULK_LOGGED bleiben, wird die Größe der nächsten Protokollsicherung nicht geändert, da alle Datenerweiterungen erfasst werden, die durch minimal protokollierte Vorgänge geändert wurden. Wow - bisher hat jede Antwort darauf verzichtet. - Paul Randal
NEIN, definitiv nicht zu einfach wechseln. Er fragte nach der Größe seiner Transaktionsprotokollsicherungen, nicht nach der Größe seiner vollständigen Sicherungen oder nach seiner Transaktionsprotokolldatei. - Brent Ozar
Wie reduzieren Sie also die Größe der Transaktionsprotokollsicherungen? Sie enthalten alles seit der letzten Protokollsicherung, es sei denn, Sie unterbrechen die Protokollsicherungskette und starten sie dann mit der vollständigen Sicherung neu. - Paul Randal
Es sei denn, Ihre Indexpflege tut nichts ... - Paul Randal


Einfache Antwort: Ändern Sie Ihren wöchentlichen Optimierungsjob, um ihn nachts ausgewogener zu betreiben. d. h. die Tische a-e am Sonntagabend, f-l am Montagabend usw. neu indizieren ... ein gutes Gleichgewicht finden, Ihr Logbuch wird etwa 1/6 der durchschnittlichen Größe sein. Dies funktioniert natürlich am besten, wenn Sie nicht den integrierten ssis-Indexwartungsjob verwenden.

Der Nachteil dabei ist, dass es abhängig von der Last, die Ihre db-Erfahrung macht, von Bedeutung ist, dass es den Optimierer und die Wiederverwendung von Abfrageplänen verheert.

Aber wenn alles, was Sie interessieren, die Größe Ihres t-Logs auf einer wöchentlichen Basis ist, teilen Sie es von Tag zu Tag oder von Stunde zu Stunde auf und führen Sie die T-Log-Backups dazwischen aus.


2
2018-05-27 18:46





Sie können auch in ein Drittanbieter-Tool (Litespeed von Quest, SQL Backup von Red Gate, Hyperbac) schauen, um die Größe der Backups und Protokolle zu reduzieren. Sie können sich schnell in Band sparen.


1
2018-05-27 18:05





Können Sie Ihr Transaktionslog an verschiedenen Stellen während Ihrer Datenbankoptimierung speziell sichern? Die Gesamtgröße der T-Logs wäre gleich, aber jeder wäre kleiner und könnte Ihnen auf irgendeine Weise helfen.

Können Sie eine gezieltere Datenbankoptimierung durchführen, so dass weniger Transaktionen erstellt werden (jemand hat dies erwähnt, aber ich bin mir nicht sicher, ob die Implikationen klar sind). So wie eine gewisse Fragmentierung oder verschwendete Fläche für eine Weile zu tolerieren. Wenn 40% Ihrer Tabellen nur zu 5% fragmentiert sind, können Sie durch das Nichtberühren dieser Tabellen ziemlich viel Aktivität sparen.

Können Sie Ihre Optimierung täglich kurz vor oder nach dem Differential (oder voll) durchführen, so dass weniger als 1 Tag seit dem letzten Mal anstelle von 7 zu optimieren ist?

Können Sie 1/7 der Optimierung kurz vor oder nach jedem Differential machen (vielleicht durch Tabellen aufgeteilt), so dass jede Woche alle Optimierungen einmal durchlaufen werden?


1
2018-05-27 18:37





Es kann wahrscheinlich angenommen werden, dass Ihre "Optimierungen" Indexwiederaufbauten enthalten. Die wöchentliche Ausführung dieser Aufgaben kann in einer Datenbank akzeptabel sein, in der es nicht viele Updates und Einfügungen gibt. Wenn Ihre Daten jedoch sehr flüssig sind, können Sie einige Dinge tun:

  1. Erstellen oder reorganisieren Sie Ihre Indizes jede Nacht neu, wenn Ihr Zeitplan dies zulässt und die Auswirkungen akzeptabel sind. Wenn Sie diese nächtlichen Indexwartungsaufgaben ausführen, richten Sie nur solche Indizes aus, die bei Neuaufstellungen über 30% und bei Neuaufträgen zwischen 15 und 30% fragmentiert sind.

  2. Bei diesen Aufgaben handelt es sich um protokollierte Transaktionen. Wenn Sie sich also Gedanken über das Log-Wachstum machen, dann würde ich befürworten, was Paul empfohlen hat. Abschließende Transaktionsprotokollsicherung vor der Indexwartung, wechseln zu Einfache Wiederherstellung, gefolgt vom Wartungsprozess und dann zurück zu Vollständige Wiederherstellung, gefolgt von einer vollständigen Datensicherung.

Ich verwende einen zen-ähnlichen Ansatz für meine Protokolldateien: Sie sind die Größe, die sie sein möchten. So lange sie kein übermäßiges Wachstum aufgrund schlechter Backup-Praktiken im Vergleich zur Datenbank-Aktivität erlitten haben, ist das Mantra, von dem ich lebe.

Was die Skripte angeht, die die diskrete Indexwartung durchführen, schauen Sie online: Es gibt eine Tonne da draußen. Andrew Kelly hat vor etwa einem Jahr im SQL Magazine ein anständiges Exemplar veröffentlicht. SQLServerPedia hat einige Skripte von Michelle Ufford, und die neueste Ausgabe des SQL Magazine (Juli 2009 glaube ich) hat einen vollständigen Artikel zu diesem Thema. Ziel ist es, einen zu finden, der gut für Sie funktioniert und ihn mit minimalen Anpassungen zu Ihrem Eigenen macht.


1
2018-06-03 20:10