Warum wächst das Transaktionsprotokoll weiter oder es ist kein Speicherplatz mehr verfügbar?


207

Diese Frage scheint in den meisten Foren und im gesamten Web eine häufig gestellte Frage zu sein. Sie wird hier in vielen Formaten gestellt, die normalerweise so klingen:

In SQL Server -

  • Was sind einige Gründe, warum das Transaktionsprotokoll so groß wird?
  • Warum ist meine Protokolldatei so groß?
  • Wie kann dieses Problem verhindert werden?
  • Was mache ich, wenn ich mich auf die zugrunde liegende Ursache einlasse und meine Transaktionsprotokolldatei auf eine gesunde Größe bringen möchte?
  0

Die wirklich kurze Antwort lautet: Versetzen Sie die Datenbank in den ** einfachen ** Modus (nicht in den ** vollständigen ** Modus).Wenn Sie zwischen Ihrer vollständigen nächtlichen Sicherung tagsüber nicht mehrere Transaktionsprotokollsicherungen durchführen, ist der Modus ** Vollständig ** nicht erforderlich. 07 dez. 162016-12-07 14:48:40

  0

@ IanBoyd - Sicher ist das die einfachste Antwort.Der Schlüssel ist jedoch, was das bedeutet.Darauf bin ich in der Antwort gekommen.Leider sprechen viel zu viele Menschen dies entweder nie an oder wechseln einfach zu simple, ohne zu verstehen warum.Ich bearbeite meine Antwort, um den einfachen Modus etwas früher zu aktivieren. 07 dez. 162016-12-07 14:53:55

270

Eine kürzere Antwort:

Sie haben wahrscheinlich entweder eine lange laufende Transaktion (Indexpflege? Löschen oder Aktualisieren von großen Stapeln?) Oder Sie befinden sich im Wiederherstellungsmodus "Standard" (mehr dazu weiter unten unter "Standard") vonFullund haben nicht ein genommenProtokollsicherung(oder nehmen sie nicht häufig genug).

Wenn es sich um ein Problem mit dem Wiederherstellungsmodell handelt, lautet die einfache Antwort möglicherweise Wechseln zuSimpleWiederherstellungsmodus, wenn Sie keine Wiederherstellung zu einem bestimmten Zeitpunkt und keine regelmäßigen Protokollsicherungen benötigen.Viele Menschen machen dies jedoch zu ihrer Antwort, ohne die Wiederherstellungsmodelle zu verstehen.Lesen Sie weiter, um zu verstehen, warum es wichtig ist, und entscheiden Sie dann, was Sie tun.Sie können auch einfach mit dem Erstellen von Protokollsicherungen beginnen und dabei bleibenFullWiederherstellung.

Es könnte andere Gründe geben, aber diese sind die häufigsten.Diese Antwort geht auf die beiden häufigsten Gründe ein und gibt Ihnen einige Hintergrundinformationen zum Warum und Wie der Gründe sowie einige andere Gründe.


Eine längere Antwort:Welche Szenarien können dazu führen, dass das Protokoll weiter wächst?Es gibt viele Gründe, aber normalerweise gibt es zwei Gründe: Es gibt ein Missverständnis bezüglich der Wiederherstellungsmodelle oder es gibt lange laufende Transaktionen.Lesen Sie weiter für Details.

Hauptgrund 1/2: Wiederherstellungsmodelle nicht verstehen

(Drin seinVollständiger Wiederherstellungsmodusund nicht nehmenProtokollsicherungen- Dies ist der häufigste Grund - die überwiegende Mehrheit der Betroffenen dieses Problems sind.)

Während diese Antwort in SQL Server-Wiederherstellungsmodellen keine große Rolle spielt, ist das Thema Wiederherstellungsmodelle für dieses Problem von entscheidender Bedeutung.

In SQL Server gibt es dreirecovery models:

  • Full,
  • Bulk-Loggedund
  • Simple.

Wir werden ignorierenBulk-LoggedFür den Moment werden wir sagen, es ist ein Hybridmodell und die meisten Leute, die in diesem Modell sind, sind aus einem Grund da und verstehen Wiederherstellungsmodelle.

Die beiden, die uns wichtig sind, und ihre Verwirrung sind die Ursache für die Mehrzahl der Fälle, in denen Menschen mit diesem Problem zu kämpfen habenSimpleundFull.

Pause: Erholung im Allgemeinen

Bevor wir über Wiederherstellungsmodelle sprechen: Lassen Sie uns über die Wiederherstellung im Allgemeinen sprechen.Wenn Sie mit diesem Thema noch tiefer gehen möchten, lesen Sie einfachPaul Randal's blogund so viele Beiträge wie du willst.Für diese Frage jedoch:

  1. Wiederherstellung nach Absturz/Neustart
    Ein Zweck der Transaktionsprotokolldatei ist fürWiederherstellung nach Absturz/Neustart.Für das Vorwärts- und Zurückrollen von Arbeit, die vor einem Absturz oder Neustart ausgeführt wurde (Vorwärtsrollen/Wiederherstellen), und für die Arbeit, die nach einem Absturz oder Neustart gestartet, aber nicht beendet wurde (Zurückrollen/Rückgängigmachen).Es ist die Aufgabe des Transaktionsprotokolls, festzustellen, ob eine Transaktion gestartet, aber nie beendet wurde (ein Rollback oder ein Absturz/Neustart erfolgte, bevor die Transaktion festgeschrieben wurde).In dieser Situation ist es die Aufgabe des Protokolls zu sagen"Hey ... das ist nie wirklich fertig, lass es uns zurückrollen."während der Genesung.Es ist auch die Aufgabe des Protokolls, zu überprüfen, ob Sie etwas fertiggestellt haben und ob Ihrer Client-Anwendung mitgeteilt wurde, dass es fertiggestellt wurde (auch wenn es noch nicht in Ihrer Datendatei verankert war) und zu sagen"Hey ... das ist wirklich passiert, lass es uns vorwärts rollen, lass es uns so machen, wie die Anwendungen denken, dass es war."nach einem Neustart.Jetzt gibt es mehr, aber das ist der Hauptzweck.

  2. Zeitpunkt der Wiederherstellung
    Der andere Zweck für eine Transaktionsprotokolldatei besteht darin, uns die Möglichkeit zu geben, eine wiederherzustellenZeitpunktaufgrund eines "Hoppla" in einer Datenbank oder um einen Wiederherstellungspunkt im Falle eines Hardwarefehlers zu gewährleisten, an dem die Daten und/oder Protokolldateien einer Datenbank beteiligt sind.Wenn dieses Transaktionsprotokoll die Aufzeichnungen von Transaktionen enthält, die für die Wiederherstellung gestartet und beendet wurden, kann und verwendet SQL Server diese Informationen, um eine Datenbank an den Ort zu bringen, an dem sie vor dem Auftreten eines Problems war.Aber das ist für uns nicht immer eine Option.Damit das funktioniert, müssen wir unsere Datenbank im richtigen Bereich habenWiederherstellungsmodellund wir müssen nehmenProtokollsicherungen.

Wiederherstellungsmodelle

Auf den Wiederherstellungsmodellen:

  • Einfaches Wiederherstellungsmodell
    Mit der obigen Einführung ist es am einfachsten, darüber zu sprechenSimple RecoveryModell zuerst.In diesem Modell teilen Sie SQL Server Folgendes mit:"Es ist in Ordnung, wenn Sie Ihre Transaktionsprotokolldatei zum Absturz bringen und die Wiederherstellung neu starten ..."(Da hast du wirklich keine andere Wahl. Schau aufACID propertiesund das sollte schnell Sinn machen.)"... aber sobald Sie es nicht mehr für diesen Absturz-/Neustart-Wiederherstellungszweck benötigen, können Sie die Protokolldatei wiederverwenden."

    SQL Server überwacht diese Anforderung in Simple Recovery und speichert nur die Informationen, die für die Wiederherstellung nach einem Absturz/Neustart erforderlich sind.Sobald SQL Server sicher ist, dass es wiederhergestellt werden kann, da die Daten in der Datendatei (mehr oder weniger) gesichert sind, sind die gesicherten Daten im Protokoll nicht mehr erforderlich und werden zum Abschneiden markiert. Dies bedeutet, dass sie wiederverwendet werden.

  • Vollständiges Wiederherstellungsmodell
    MitFull Recovery, Sie teilen SQL Server mit, dass die Wiederherstellung zu einem bestimmten Zeitpunkt möglich sein soll, sofern Ihre Protokolldatei verfügbar ist, oder zu einem bestimmten Zeitpunkt, der von einer Protokollsicherung abgedeckt wird.In diesem Fall wird SQL Server dies nicht tun, wenn der Punkt erreicht ist, an dem das Abschneiden der Protokolldatei in Simple Recovery Model sicher ist.StattdessenDadurch wächst die Protokolldatei weiterund wird es weiter wachsen lassen,bis Sie eine Log-Sicherung machen(oder nicht genügend Speicherplatz auf Ihrem Protokolldateilaufwerk) unter normalen Umständen.

Das Umschalten von Einfach auf Voll hat ein Gotcha.

Hier gibt es Regeln und Ausnahmen.Wir werden im Folgenden ausführlich über langfristige Transaktionen sprechen.

Eine Einschränkung, die Sie beim vollständigen Wiederherstellungsmodus beachten sollten, ist die folgende: Wenn Sie nur in wechselnFull RecoveryModus, aber nehmen Sie nie eine erste vollständige Sicherung, SQL Server wirdnichtehre deine Bitte, dabei zu seinFull RecoveryModell.Ihr Transaktionslog funktioniert weiterhin wie bisherSimplebis Sie zum vollständigen Wiederherstellungsmodell wechseln UND das erste nehmenFull Backup.

Das vollständige Wiederherstellungsmodell ohne Protokollsicherungen ist fehlerhaft.

Das ist also der häufigste Grund für unkontrolliertes Holzwachstum?Antwort: Sie befinden sich im vollständigen Wiederherstellungsmodus, ohne Protokollsicherungen durchzuführen.

Das passiertallesdie Zeit für die Menschen.

Warum ist das so ein häufiger Fehler?

Warum passiert es die ganze Zeit?Weil jede neue Datenbank ihre ursprüngliche Einstellung für das Wiederherstellungsmodell erhält, indem sie sich die Modelldatenbank ansieht.

Die anfängliche Einstellung für das Wiederherstellungsmodell des Modells ist immerFull Recovery Model- bis und bis jemand das ändert.Man könnte also sagen, das "Standard-Wiederherstellungsmodell" istFull.Viele Menschen sind sich dessen nicht bewusst und haben ihre Datenbanken in BetriebFull Recovery ModelOhne Protokollsicherungen und daher mit einer Transaktionsprotokolldatei, die viel größer als erforderlich ist.Aus diesem Grund ist es wichtig, die Standardeinstellungen zu ändern, wenn sie für Ihr Unternehmen und dessen Anforderungen nicht funktionieren.

Das vollständige Wiederherstellungsmodell mit zu wenigen Protokollsicherungen ist fehlerhaft.

Sie können sich hier auch in Schwierigkeiten bringen, indem Sie nicht häufig genug Protokollsicherungen durchführen.
Wenn Sie ein Protokoll-Backup pro Tag erstellen, klingt dies möglicherweise in Ordnung, da für eine Wiederherstellung weniger Wiederherstellungsbefehle erforderlich sind. Beachten Sie jedoch, dass die Protokolldatei so lange wächst, bis Sie Protokoll-Backups erstellen.

Wie finde ich heraus, welche Häufigkeit der Protokollsicherung ich benötige?

Sie müssen die Häufigkeit Ihrer Protokollsicherungen unter zwei Gesichtspunkten berücksichtigen:

  1. Wiederherstellungsbedarf- Dies sollte hoffentlich zuerst sein.Wie viele Daten können verloren gehen, wenn das Laufwerk, auf dem sich Ihr Transaktionsprotokoll befindet, fehlerhaft ist oder Sie eine schwerwiegende Beschädigung feststellen, die sich auf Ihre Protokollsicherung auswirkt?Wenn diese Anzahl nicht mehr als 10-15 Minuten beträgt, müssen Sie alle 10-15 Minuten eine Protokollsicherung durchführen, Ende der Diskussion.
  2. Log Wachstum- Wenn Ihr Unternehmen mehr Daten verlieren kann, weil es an diesem Tag problemlos wiederhergestellt werden kann, ist es möglicherweise in Ordnung, eine Protokollsicherung viel seltener als 15 Minuten durchzuführen.Vielleicht ist Ihre Organisation alle 4 Stunden in Ordnung.Sie müssen sich jedoch ansehen, wie viele Transaktionen Sie in 4 Stunden generieren.Wird eine Protokolldatei zu groß, wenn das Protokoll in diesen vier Stunden weiter wächst?Dauern Ihre Protokollsicherungen dann zu lange?

Hauptgrund 2/2: Langfristige Transaktionen

("Mein Wiederherstellungsmodell ist in Ordnung! Das Protokoll wächst noch!)

Dies kann auch eine Ursache für unkontrolliertes und ungebremstes Holzwachstum sein.Egal welches Wiederherstellungsmodell, aber es kommt oft vor wie"Aber ich bin im einfachen Wiederherstellungsmodell - warum wächst mein Protokoll immer noch ?!"

Der Grund hierfür ist einfach: Wenn SQL dieses Transaktionsprotokoll wie oben beschrieben für Wiederherstellungszwecke verwendet, muss es zum Beginn einer Transaktion zurückkehren.

Wenn Sie über eine Transaktion verfügen, die viel Zeit in Anspruch nimmt oder viele Änderungen vornimmt, kann das Protokoll am Prüfpunkt nicht für Änderungen abgeschnitten werden, die sich noch in offenen Transaktionen befinden oder die seit dem Start dieser Transaktion gestartet wurden.

Dies bedeutet, dass ein großer Löschvorgang, bei dem Millionen von Zeilen in einer Löschanweisung gelöscht werden, eine Transaktion ist und das Protokoll erst dann abgeschnitten werden kann, wenn der gesamte Löschvorgang abgeschlossen ist.ImFull Recovery Modelwird dieser Löschvorgang protokolliert und das können viele Protokollsätze sein.Dasselbe gilt für die Indexoptimierung während Wartungsfenstern.Dies bedeutet auch, dass eine schlechte Transaktionsverwaltung und das Nichtbeachten und Schließen offener Transaktionen Sie und Ihre Protokolldatei wirklich verletzen können.

Was kann ich gegen diese langfristigen Transaktionen tun?

Sie können sich hier sparen, indem Sie:

  • Ordnungsgemäße Dimensionierung Ihrer Protokolldatei, um das Worst-Case-Szenario zu berücksichtigen - z. B. Ihre Wartung oder bekannte große Vorgänge.Und wenn Sie Ihre Protokolldatei vergrößern, sollten Sie sich das ansehenguidance(und die beiden Links, zu denen sie Sie schickt) von Kimberly Tripp.Die richtige Dimensionierung ist hier sehr wichtig.
  • Beobachten Sie Ihre Verwendung von Transaktionen.Starten Sie keine Transaktion auf Ihrem Anwendungsserver und führen Sie keine langen Unterhaltungen mit SQL Server. Andernfalls besteht die Gefahr, dass eine zu lange offen bleibt.
  • Schauen die ... animplizite Transaktionenin Ihren DML-Anweisungen.Zum Beispiel:UPDATE TableName Set Col1 = 'New Value'ist eine Transaktion.Ich habe keine gesetztBEGIN TRANda und ich muss nicht, es ist immer noch eine Transaktion, die nur automatisch festgeschrieben wird, wenn sie abgeschlossen ist.Wenn Sie also Vorgänge mit einer großen Anzahl von Zeilen ausführen, sollten Sie erwägen, diese Vorgänge in übersichtlichere Blöcke zu unterteilen und dem Protokoll Zeit für die Wiederherstellung zu geben.Oder überlegen Sie sich die richtige Größe, um damit umzugehen.Oder untersuchen Sie das Ändern von Wiederherstellungsmodellen während eines Massenladefensters.

Treffen diese beiden Gründe auch auf den Holzversand zu?

Kurze Antwort: ja.Längere Antwort unten.

Frage:"Ich verwende den Protokollversand, daher werden meine Protokollsicherungen automatisiert. Warum sehe ich immer noch ein Wachstum des Transaktionsprotokolls?"

Antwort: lesen Sie weiter.

Was ist Holzversand?

Der Protokollversand ist genau das, wonach es sich anhört - Sie versenden Ihre Transaktionsprotokollsicherungen zu DR-Zwecken an einen anderen Server.Es gibt einige Initialisierungen, aber danach ist der Vorgang ziemlich einfach:

  • Ein Auftrag zum Sichern des Protokolls auf einem Server,
  • einen Auftrag zum Kopieren dieser Protokollsicherung und
  • ein Job, um es ohne Wiederherstellung wiederherzustellen (entwederNORECOVERYoderSTANDBY) auf dem Zielserver.

Es gibt auch einige Jobs, die überwacht und alarmiert werden müssen, wenn die Dinge nicht so laufen, wie Sie es geplant haben.

In einigen Fällen möchten Sie die Wiederherstellung des Protokollversands möglicherweise nur einmal am Tag, alle drei Tage oder jede Woche durchführen.Das ist gut.Wenn Sie diese Änderung jedoch für alle Jobs (einschließlich der Protokollsicherungs- und Kopierjobs) vornehmen, bedeutet dies, dass Sie die ganze Zeit auf eine Protokollsicherung warten.Das bedeutet, dass Sie viel Stammwachstum haben werden - weil Sie es sindim vollständigen Wiederherstellungsmodus ohne Protokollsicherungen- und es bedeutet wahrscheinlich auch, dass eine große Protokolldatei kopiert werden muss.Sie sollten nur den Zeitplan des Wiederherstellungsjobs ändern und die Protokollsicherungen und -kopien häufiger ausführen lassen, da sonst das erste in dieser Antwort beschriebene Problem auftritt.


Allgemeine Fehlersuche über Statuscodes

Es gibt andere Gründe als diese beiden, aber diese sind die häufigsten.Unabhängig von der Ursache: Es gibt eine Möglichkeit, den Grund für dieses ungeklärte Protokollwachstum bzw. das Fehlen von Kürzungen zu analysieren und die Ursache zu ermitteln.

Durch Abfragen dersys.databasesIn der Katalogansicht werden Informationen angezeigt, die beschreiben, warum Ihre Protokolldatei möglicherweise auf das Abschneiden/Wiederverwenden wartet.

Es gibt eine Spalte namenslog_reuse_waitmit einer Nachschlage-ID des Ursachencodes und alog_reuse_wait_descSpalte mit einer Beschreibung des Wartegrundes.In den Online-Artikeln zu den Büchern, auf die verwiesen wird, sind die meisten Gründe (die Sie wahrscheinlich sehen und die, für die wir Gründe erklären können. Die fehlenden sind entweder nicht in Gebrauch oder für den internen Gebrauch) mit ein paar Anmerkungen zum Warten inKursivschrift:

  • 0 = Nichts
    Wie es sich anhört. Sollte nicht warten

  • 1 = Checkpoint
    Warten auf einen Checkpoint.Dies sollte passieren und Sie sollten in Ordnung sein - aber es gibt einige Fälle, nach denen Sie hier nach späteren Antworten oder Änderungen suchen müssen.

  • 2 = Protokollsicherung
    Sie warten auf eine Protokollsicherung.Entweder haben Sie sie geplant und es wird bald passieren, oder Sie haben das erste hier beschriebene Problem und wissen jetzt, wie Sie es beheben können

  • 3 = Aktive Sicherung oder Wiederherstellung
    Auf der Datenbank wird ein Sicherungs- oder Wiederherstellungsvorgang ausgeführt

  • 4 = Aktive Transaktion
    Es ist eine aktive Transaktion vorhanden, die abgeschlossen werden muss (in beiden Fällen -ROLLBACKoderCOMMIT), bevor das Protokoll gesichert werden kann.Dies ist der zweite Grund, der in dieser Antwort beschrieben wird.

  • 5 = Datenbankspiegelung
    Entweder kommt ein Spiegel in einer Hochleistungsspiegelungssituation hinterher oder es tritt eine gewisse Latenz auf, oder die Spiegelung wird aus irgendeinem Grund angehalten

  • 6 = Replikation
    Es kann Probleme mit der Replikation geben, die dies verursachen könnten - beispielsweise, dass ein Protokolllese-Agent nicht ausgeführt wird, eine Datenbank denkt, dass er für eine Replikation markiert ist, die nicht mehr ausgeführt wird, und verschiedene andere Gründe.Sie können diesen Grund auch erkennen, und es ist völlig normal, dass Sie genau zum richtigen Zeitpunkt suchen, so wie Transaktionen vom Protokollleser verarbeitet werden

  • 7 = Datenbank-Snapshot-Erstellung
    Sie erstellen einen Datenbank-Snapshot. Dies wird angezeigt, wenn Sie genau den richtigen Zeitpunkt für die Erstellung eines Snapshots auswählen

  • 8 = Log Scan
    Ich habe noch kein Problem damit, für immer mitzulaufen.Wenn Sie lange genug und häufig genug nachsehen, können Sie dies beobachten, aber es sollte keine Ursache für übermäßiges Wachstum des Transaktionsprotokolls sein, wie ich gesehen habe.

  • 9 = Ein sekundäres Replikat von AlwaysOn Availability Groups wendet Transaktionsprotokollsätze dieser Datenbank auf eine entsprechende sekundäre Datenbank an.Über die klarste Beschreibung noch ..

+1

Seitenteile erhöhen die Protokollierung.Ein wichtiger Grund (meiner Erfahrung nach), der für ein großes Wachstum, das häufig schrumpfen muss und das in vielen meiner Fälle behoben wurde, nicht erwähnt wurde, ist die Verwendung der richtigen Indexauswahl, einschließlich der richtigen FillFactor-Mgmt.Ich verwende die folgenden Einstellungen unter genauer Beobachtung.FF-Einstellungen: (0/100) Tabellen mit High-Reads/Low-Writes, (90) für leicht modifizierte, (80) Medium-Reads/Low-Med-Writes, (70) High-Writes, (60) Ich erreiche dies kaum Level oder etwas anderes könnte falsch sein.Verwenden Sie dann die Indexverwaltungszeitpläne, die dem Datenvolumen entsprechen. 08 okt. 152015-10-08 19:31:03


98

Da bin ich mit keiner der Antworten wirklich zufriedenover on Stack OverflowIch dachte, ich würde auch hier meinen Beitrag leisten, auch weil ich einige Dinge ansprechen möchte, auf die Mikes Antwort nicht zutrifft.Dort habe ich auch eine Kopie dieser Antwort abgelegt.

Das Verkleinern einer Protokolldatei sollte wirklich für Szenarien reserviert werden, in denen unerwartetes Wachstum auftritt, von dem Sie nicht erwarten, dass es erneut auftritt.Wenn die Protokolldatei wieder dieselbe Größe annimmt, wird nicht viel erreicht, indem sie vorübergehend verkleinert wird.Nun, abhängig von den Wiederherstellungszielen Ihrer Datenbank, sind dies die Maßnahmen, die Sie ergreifen sollten.

Führen Sie zunächst eine vollständige Sicherung durch

Nehmen Sie niemals Änderungen an Ihrer Datenbank vor, ohne sicherzustellen, dass Sie sie wiederherstellen können, falls etwas schief gehen sollte.

Wenn Ihnen die Wiederherstellung zu einem bestimmten Zeitpunkt wichtig ist

(Unter Wiederherstellung zu einem bestimmten Zeitpunkt verstehe ich, dass es Ihnen wichtig ist, auf etwas anderem als einer vollständigen oder differenziellen Sicherung wiederherstellen zu können.)

Vermutlich befindet sich Ihre Datenbank inFULLWiederherstellungsmodus.Wenn nicht, stellen Sie sicher, dass es ist:

ALTER DATABASE yourdb SET RECOVERY FULL;

Selbst wenn Sie regelmäßig vollständige Sicherungen durchführen, wird die Protokolldatei so lange erweitert, bis Sie eineLogBackup - Dies dient Ihrem Schutz, damit Sie nicht unnötig Speicherplatz verlieren.Sie sollten diese Protokollsicherungen entsprechend Ihren Wiederherstellungszielen häufig durchführen.Wenn Sie beispielsweise eine Geschäftsregel haben, die besagt, dass Sie es sich leisten können, im Katastrophenfall mindestens 15 Minuten an Daten zu verlieren, sollten Sie einen Auftrag haben, der das Protokoll alle 15 Minuten sichert.Hier ist ein Skript, das zeitgestempelte Dateinamen basierend auf der aktuellen Zeit generiert (Sie können dies aber auch mit Wartungsplänen usw. tun, wählen Sie einfach keine der Verkleinerungsoptionen in Wartungsplänen aus, sie sind schrecklich).

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
    + CONVERT(CHAR(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
    + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Beachten Sie, dass\\backup_share\sollte sich auf einem anderen Computer befinden, der ein anderes zugrunde liegendes Speichergerät darstellt.Das Sichern dieser Daten auf demselben Computer (oder auf einem anderen Computer, der dieselben zugrunde liegenden Datenträger verwendet, oder auf einer anderen VM, die sich auf demselben physischen Host befindet) hilft Ihnen nicht wirklich, da Sie Ihre Datenbank verloren haben, wenn der Computer in die Luft gehtundseine Backups.Abhängig von Ihrer Netzwerkinfrastruktur ist es möglicherweise sinnvoller, lokal zu sichern und diese dann hinter den Kulissen an einen anderen Ort zu übertragen.In beiden Fällen möchten Sie sie so schnell wie möglich vom primären Datenbankcomputer entfernen.

Sobald Sie regelmäßige Protokollsicherungen ausgeführt haben, sollte es sinnvoll sein, die Protokolldatei auf etwas Vernünftigeres zu verkleinern, als dies bisher der Fall war.Das machtnichtmeine laufenSHRINKFILEimmer und immer wieder, bis die Protokolldatei 1 MB groß ist - auch wenn Sie das Protokoll häufig sichern, muss die Summe aller gleichzeitig auftretenden Transaktionen berücksichtigt werden.Ereignisse für das automatische Anwachsen von Protokolldateien sind teuer, da SQL Server die Dateien auf Null setzen muss (im Gegensatz zu Datendateien, wenn die sofortige Dateiinitialisierung aktiviert ist) und Benutzertransaktionen warten müssen, bis dies geschieht.Sie möchten diese Routine zum Vergrößern, Verkleinern, Vergrößern und Verkleinern so wenig wie möglich ausführen und möchten sicher nicht, dass Ihre Benutzer dafür bezahlen.

Beachten Sie, dass Sie das Protokoll möglicherweise zweimal sichern müssen, bevor eine Verkleinerung möglich ist (danke Robert).

Sie müssen sich also eine praktische Größe für Ihre Protokolldatei ausdenken.Niemand hier kann Ihnen sagen, was das ist, ohne viel mehr über Ihr System zu wissen, aber wenn Sie die Protokolldatei häufig verkleinert haben und sie wieder gewachsen ist, ist ein gutes Wasserzeichen wahrscheinlich 10-50% höher als das größte .Nehmen wir an, es sind 200 MB, und Sie möchten, dass alle nachfolgenden Autogrowth-Ereignisse 50 MB betragen. Dann können Sie die Größe der Protokolldatei folgendermaßen anpassen:

USE [master];
GO
ALTER DATABASE Test1 
    MODIFY FILE
    (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

Beachten Sie, dass Sie möglicherweise zuerst Folgendes ausführen müssen, wenn die Protokolldatei derzeit> 200 MB ist:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

Wenn Ihnen die Wiederherstellung zu einem bestimmten Zeitpunkt nicht wichtig ist

Wenn es sich um eine Testdatenbank handelt und Sie sich nicht für die Wiederherstellung zu einem bestimmten Zeitpunkt interessieren, sollten Sie sicherstellen, dass sich Ihre Datenbank in befindetSIMPLEWiederherstellungsmodus.

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

Putting die Datenbank inSIMPLEDer Wiederherstellungsmodus stellt sicher, dass SQL Server Teile der Protokolldatei wiederverwendet (im Wesentlichen inaktive Transaktionen auslaufen lässt), anstatt zu wachsen, um Aufzeichnungen zu führenallesTransaktionen (wieFULLDie Wiederherstellung erfolgt, bis Sie das Protokoll sichern.CHECKPOINTEreignisse helfen dabei, das Protokoll zu steuern und sicherzustellen, dass es nicht erweitert werden muss, es sei denn, Sie generieren zwischen diesen Ereignissen viel T-Log-AktivitätCHECKPOINTs.

Als Nächstes sollten Sie unbedingt sicherstellen, dass dieses Protokollwachstum tatsächlich auf ein abnormales Ereignis zurückzuführen ist (z. B. einen jährlichen Frühjahrsputz oder die Wiederherstellung Ihrer größten Indizes) und nicht auf die normale, alltägliche Verwendung.Was haben Sie gewonnen, wenn Sie die Protokolldatei auf eine lächerlich kleine Größe verkleinern und SQL Server sie nur erneut vergrößern muss, um Ihre normale Aktivität zu unterstützen?Konnten Sie den von Ihnen freigegebenen Speicherplatz nur vorübergehend nutzen?Wenn Sie eine sofortige Korrektur benötigen, können Sie Folgendes ausführen:

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

Stellen Sie andernfalls eine geeignete Größe und Wachstumsrate ein.Wie im Beispiel für die Wiederherstellung zu einem bestimmten Zeitpunkt beschrieben, können Sie denselben Code und dieselbe Logik verwenden, um die geeignete Dateigröße zu ermitteln und angemessene Parameter für das automatische Wachstum festzulegen.

Einige Dinge, die Sie nicht tun möchten

  • Sichern Sie das Protokoll mitTRUNCATE_ONLYOption und dannSHRINKFILE.Zum einen dasTRUNCATE_ONLYDie Option ist veraltet und in aktuellen Versionen von SQL Server nicht mehr verfügbar.Zweitens, wenn Sie in sindFULLWiederherstellungsmodell, dies zerstört Ihre Protokollkette und erfordert eine neue, vollständige Sicherung.

  • Trennen Sie die Datenbank, löschen Sie die Protokolldatei und hängen Sie sie erneut an.Ich kann nicht betonen, wie gefährlich das sein kann.Ihre Datenbank wird möglicherweise nicht wiederhergestellt, sie wird möglicherweise als verdächtig eingestuft, Sie müssen möglicherweise auf eine Sicherung zurückgreifen (falls Sie eine haben) usw. usw.

  • Verwenden Sie die Option "Datenbank verkleinern".DBCC SHRINKDATABASEund die Wartungsplanoption, um dasselbe zu tun, sind schlechte Ideen, besonders wenn Sie wirklich nur ein Protokollproblem lösen müssen.Greifen Sie auf die Datei zu, die Sie anpassen möchten, und passen Sie sie unabhängig mit anDBCC SHRINKFILEoderALTER DATABASE ... MODIFY FILE(Beispiele oben).

  • Verkleinern Sie die Protokolldatei auf 1 MB.Das sieht verlockend aus, weil SQL Server es mir in bestimmten Szenarien erlaubt und sich den gesamten freigewordenen Speicherplatz ansieht!Sofern Ihre Datenbank nicht schreibgeschützt ist (und dies auch ist, sollten Sie sie mit als solche markieren)ALTER DATABASE), dies wird absolut nur zu vielen unnötigen Wachstumsereignissen führen, da das Protokoll aktuelle Transaktionen unabhängig vom Wiederherstellungsmodell aufnehmen muss.Was ist der Grund, diesen Speicherplatz vorübergehend freizugeben, damit SQL Server ihn langsam und schmerzhaft zurücknehmen kann?

  • Erstellen Sie eine zweite Protokolldatei.Dadurch wird das Laufwerk, das Ihre Festplatte gefüllt hat, vorübergehend entlastet. Dies entspricht jedoch dem Versuch, eine Lungenpunktion mit einem Pflaster zu beheben.Sie sollten sich direkt mit der problematischen Protokolldatei befassen, anstatt nur ein weiteres potenzielles Problem hinzuzufügen.Abgesehen davon, dass einige Transaktionsprotokollaktivitäten auf ein anderes Laufwerk umgeleitet werden, hat eine zweite Protokolldatei (im Gegensatz zu einer zweiten Datendatei) wirklich nichts für Sie zu tun, da immer nur eine der Dateien gleichzeitig verwendet werden kann.Paul Randal also explains why multiple log files can bite you later.

Sei proaktiv

Anstatt Ihre Protokolldatei auf einen kleinen Wert zu verkleinern und sie selbstständig mit einer kleinen Geschwindigkeit automatisch wachsen zu lassen, sollten Sie eine relativ große Größe festlegen (eine, die die Summe Ihrer größten gleichzeitigen Transaktionen berücksichtigt) und eine angemessene automatische Vergrößerung festlegen Einstellung als Fallback, damit es nicht mehrfach wachsen muss, um einzelne Transaktionen zu erfüllen, und damit es im normalen Geschäftsbetrieb relativ selten jemals wachsen muss.

Die schlechtesten Einstellungen sind hier 1 MB Wachstum oder 10% Wachstum.Es ist schon komisch, dass dies die Standardeinstellungen für SQL Server sind (über die ich mich beschwert habe undasked for changes to no avail) - 1 MB für Datendateien und 10% für Protokolldateien.Ersteres ist heutzutage viel zu klein, und letzteres führt jedes Mal zu immer längeren Ereignissen (z. B. beträgt Ihre Protokolldatei 500 MB, das erste Wachstum 50 MB, das nächste Wachstum 55 MB, das nächste Wachstum 60,5 MB) usw. usw. - und bei langsamer E/A, glauben Sie mir, Sie werden diese Kurve wirklich bemerken).

Weitere Lektüre

Bitte hör hier nicht auf;Während viele der Ratschläge zum Verkleinern von Protokolldateien inhärent schlecht und sogar potenziell katastrophal sind, legen einige Leute mehr Wert auf Datenintegrität als darauf, Speicherplatz freizugeben.


21

Sie können auch den Inhalt Ihrer Protokolldatei anzeigen.Dazu können Sie das undokumentierte verwendenfn_dblogoder ein Transaktionsprotokolllesegerät, wie zApexSQL Log.

Die Indexreorganisation wird nicht angezeigt, es werden jedoch alle DML- und verschiedenen DDL-Ereignisse angezeigt:ALTER,CREATE,DROP, aktivieren/deaktivieren auslösen, Berechtigungen erteilen/entziehen, Objekt umbenennen.

ApexSQLLogProject.temp - ApexSQL.log

Haftungsausschluss: Ich arbeite für ApexSQL als Support Engineer


1

Dies ist das am häufigsten auftretende Problem bei fast allen Datenbankadministratoren, bei denen die Protokolle größer werden und den Datenträger ausfüllen.

• Aus welchen Gründen wird das Transaktionsprotokoll so groß?

  1. Lange aktive Transaktion
  2. Hohe Protokollierungstransaktionen wie Indexwiederherstellung, Neuorganisation, Masseneinfügung, Löschung usw.
  3. Jeder HA wie Replication, Mirroring, der das Protokoll enthält und es nicht zulässt, den Protokollspeicher freizugeben

• Warum ist meine Protokolldatei so groß?

Überprüfen Sie für dielog_reuse_wait_desc Spalte insys.databasesTabelle, um zu wissen, was die Protokolle vom Abschneiden abhält:

select name, log_reuse_wait_desc 
from sys.databases

• Wie kann dieses Problem verhindert werden?

Mithilfe von Protokollsicherungen können Sie das Protokollwachstum kontrollieren, es sei denn, es gibt etwas, das die Wiederverwendung der Protokolle verhindert.

• Was kann ich tun, wenn ich mich auf die zugrunde liegende Ursache einlasse und meine Transaktionsprotokolldatei auf eine gesunde Größe bringen möchte?

Wenn Sie herausgefunden haben, was das Problem tatsächlich verursacht, versuchen Sie, es wie auf der folgenden Seite beschrieben zu beheben.

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

Die richtige Planung von Protokollsicherungen ist die beste Methode, um mit dem Protokollwachstum umzugehen, es sei denn, es liegt eine ungewöhnliche Situation vor.