INF: Gründe für das Anwachsen des SQL-Transaktionsprotokolls

Das SQL Server-Transaktionsprotokoll kann belegt sein, wodurch weiteres Aktualisieren, Löschen oder Einfügen in die Datenbank, einschließlich des Prüfpunkts, verhindert wird. Dies zeigt sich normalerweise als Fehler 1105: Dieses Ereignis kann bei jeder Datenbank auftreten, einschließlich master oder tempdb. In diesem Artikel werden mögliche Gründe und Lösungsansätze für die Probleme, die den Fehler 1105 ausgelöst haben, erörtert. Wenn Ihr Transaktionsprotokoll angewachsen ist und Sie den Fehler 1105 erhalten, müssen Sie das Protokoll leeren, indem Sie die Anweisung DUMP TRANSACTION verwenden. Weitere Informationen über die Verwendung der Anweisung DUMP TRANSACTION finden Sie in der Dokumentation zu SQL Server.

Ein wesentliches Merkmal echter relationaler Datenbanken wie Microsoft SQL Server stellt die transaktionale Integrität dar. Jede Transaktion muss vollständig atomar sein (d. h. funktional unteilbar) in dem Sinne, dass alle von ihr durchgeführten Änderungen entweder übernommen oder verworfen werden müssen, und zwar selbst im Fall eines Systemfehlers. Bei einer benutzerdefinierten Transaktion werden sämtliche Anweisungen, die von den Anweisungen BEGIN TRANSACTION und COMMIT TRANSACTION eingeschlossen sind, entweder ausgeführt oder nicht. In einer impliziten Transaktion wird jede einzelne SQL-Anweisung als atomare Einheit angesehen.

Dies ermöglicht es SQL Server, die Datenbank nach einem Stromausfall, Betriebssystemausfall usw. während der Produktion und nach einem Neustart automatisch in einen konsistenten Zustand zu bringen, ohne dass ein Eingreifen von Anwenderseite erforderlich ist. Anders bei nicht-relationalen Systemen, bei denen häufig langwierige, manuelle Verfahren notwendig sind, um die Datenbank auf Konsistenzprobleme nach einem Systemfehler zu überprüfen.

Bereitgestellt wird diese Fähigkeit vom Transaktionsprotokoll-Mechanismus. Mit der transaktionalen Integrität als wesentliches, wichtiges Merkmal von SQL Server ist ein Deaktivieren der Protokollierung nicht vorgesehen. Bestimmte Dienstprogramm- oder Verwaltungsoperationen, wie beispielsweise schnelles BCP und SELECT INTO, verfügen über minimale Protokollfunktionen, die jedoch immerhin die Blockreservierung aufzeichnen, damit ein Zurücksetzen der Transaktion möglich ist.

Die Speicherplatzanforderungen beim Protokollieren können beträchtlich sein. In den meisten Fällen müssen die Zustände aller aktualisierten Datenzeilen vor und nach der Änderung aufgezeichnet werden, zuzüglich der Zeilen mit den betroffenen Indizes. Da eine bestimmte Festgröße für allgemeine Transaktionsvermerke jeder protokollierten Zeile notwendig ist, variiert das Verhältnis von aktualisierten Daten zum Speicherplatz des Protokolls je nach Zeilenbreite. Bei einer engen Zeile kann der Platzbedarf des Protokolls für eine bestimmte Aktualisierung, Löschung oder Einfügung das Zehnfache des Datenplatzes beanspruchen. Bei breiteren Zeilen beträgt der Platzbedarf des Protokolls proportional weniger. Der Speicherbedarf des Protokolls ist eine unvermeidbare Konsequenz der transaktionalen Integrität. Der Datenbankadministrator muss genügend Speicherplatz für Protokolle bei der Installation bereitstellen.

Der Speicherplatzbedarf für Protokolle kann von vielen Faktoren abhängen und variieren. Es ist schwierig, ihn genau vorauszusagen. Während eine allgemeine Faustregel von beispielsweise 15 bis 30 Prozent der Datenbankgröße als Anhaltspunkt für die Bemessung eines Protokolls ausgeht, variiert dies tatsächlich in großem Umfang. Erfolgreiche SQL Server-Installationen beruhen oft auf einfachen empirischen Tests. Dabei wird der Protokollspeicherbedarf für die jeweiligen Daten und Anwendungen grob bemessen, worauf die tatsächliche Protokollgröße dann aufbaut. Ein Versuch, die Protokollgröße ausschließlich nach Berechnungen ohne Tests festzulegen, ist schwierig und führt oft zu falschen Ergebnissen.

Es gibt mehrere schwer vorhersagbare Faktoren, die zu Variationen im Protokollspeicherbedarf führen. Ein Beispiel ist die Abfrageoptimierung. Bei einer bestimmten SQL-Anweisung zur Datenmodifizierung kann der Zugriffsplan mit der Zeit aufgrund der statistischen Datenverteilung variieren. Verschiedene Zugriffspläne können unterschiedliche Protokollspeicherplatzanforderungen haben. Ein anderes Beispiel ist die unvermeidbare interne Datenbankfragmentation, welche die Anzahl der ausgeführten Seitenteilungen beeinflussen kann. Sie können oder sollten diesen Prozess nicht untersuchen oder beeinflussen, da SQL Server die Daten für den Benutzer automatisch verwaltet.

Ein Beispiel für einen einfachen Test wäre DBCC CHECKTABLE (syslogs), wobei die Anzahl der 2048-Byte-Datenseiten im Protokoll ausgegeben wird, und zwar sowohl bevor als auch nachdem ein repräsentatives Beispiel der Datenmodifikationsabfrage durchgeführt wurde. Dies kann Ihnen eine ungefähre Vorstellung vom Protokollspeicherplatzbedarf für diese Abfrageart geben. Es ist immer vorteilhafter, zu viel Protokoll- und Datenspeicherplatz für relationale Datenbanken, wie z. B. SQL Server, bereitzustellen als zu wenig.

Bei SQL Server 7.0- und SQL Server 2000-Klassenservern kann das Transaktionsprotokoll je nach Bedarf größer werden. Der Benutzer kann die Zuwachsraten verwalten und zulassen, dass die gesamte verfügbare Festplattenkapazität genutzt wird. Es wird eine Protokolldatei aus mehreren virtuellen Protokolldateien zusammengestellt. Die Anzahl und Größe dieser virtuellen Protokolldateien werden von SQL Server festgelegt und können nicht konfiguriert werden. Wenn eine Datenbank zum ersten Mal erstellt wird, hat jede physische Protokolldatei mindestens 2 virtuelle Protokolldateien. Manchmal wird die Datenbank-Option "Protokoll bei Prüfpunkt abschneiden" vom Datenbankadministrator aktiviert, um Protokollspeicherplatzknappheit zu verhindern. Diese Option stellt ein automatisches Verfahren zum Abschneiden des Protokolls bereit, das vor allem bei Datenbanken für Entwicklungs- oder Testzwecke verwendet wird, für deren Backup keine Protokollsicherungen benötigt werden. Mit dieser Option wird weder die Protokollierung noch die transaktionale Integrität deaktiviert, sondern es wird lediglich der Prüfpunkt-Handler veranlasst, das Protokoll etwa alle 60 Sekunden abzuschneiden. Dies findet jedoch nicht statt, wenn in einer Datenbank mit aktivierter Option "Protokoll bei Prüfpunkt abschneiden" ein manueller Prüfpunkt-Befehl ausgegeben wird. Für die Datenbank tempdb ist diese Option ständig aktiviert, was allerdings in der Statusspalte der Ausgabe der gespeicherten Systemprozedur sp_help nicht angezeigt wird.

Selbst wenn die Option "Protokoll bei Prüfpunkt abschneiden" aktiviert ist, können folgende Faktoren zu Protokollspeicherplatzknappheit führen:

1. Eine große atomare Transaktion, insbesondere eine Massenaktualisierung, -einfügung oder -löschung: Jede einzelne SQL-Anweisung wird als atomare Einheit betrachtet und muss als Ganzes ausgeführt oder nicht ausgeführt werden. Aus diesem Grund müssen alle Zeilenänderungen protokolliert werden, und während der Dauer der Transaktion kann diese nicht abgeschnitten werden. Wenn beispielsweise eine umfangreiche Masseneinfügung mit INSERT ausgeführt wurde, die 5 Minuten lief, kann das Protokoll, das für diese Transaktion verwendet wurde, in dieser Zeit nicht gekürzt werden. Der Datenbankadministrator muss genügend Protokollspeicherplatz für die größte zu erwartende Massenoperation bereitstellen oder die Massenoperation in kleineren Gruppen ausführen.
2. Eine nicht ausgeführte Transaktion: Das Protokoll kann nur vor der ältesten nicht ausgeführten Transaktion abgeschnitten werden. Es gibt mehrere Gründe für nicht ausgeführte Transaktionen, von denen die meisten durch Anwendungsfehler hervorgerufen werden. Beispiele hierfür sind:
   1. Eine Massentransaktion: Wie erwähnt, kann während einer umfangreichen Massentransaktion das Protokoll um die hierbei angelegten Einträge nicht gekürzt werden. Eine solche Transaktion schließt auch das Abschneiden des Protokolls bei anderen kürzeren und bereits ausgeführten Transaktionen während desselben Zeitraumes aus.
      
      Angenommen, der Datenbankadministrator hat ein Protokoll bemessen, das für die größte zu erwartende Massentransaktion ausreichend ist. Während der Transaktion verbrauchen andere kürzere Datenmodifizierungsanweisungen ebenso Speicherplatz. Der Protokollspeicherplatz kann nicht abgeschnitten werden, da die große Massentransaktion zuerst gestartet wurde und somit zur ältesten nicht ausgeführten Transaktion wird. Der Administrator muss sich des Konflikts und der Protokollauswirkungen einer großen Massentransaktion bewusst sein und den Protokollspeicherplatz ausreichend bemessen.
   2. Eine schlecht entwickelte Anwendung, die Benutzereingaben oder andere langwierige Aktivitäten innerhalb einer benutzerdefinierten Transaktion zulässt. Nachdem z. B. BEGIN TRANSACTION ausgegeben wurde, könnte eine Anwendung den Benutzer zur Eingabe auffordern, was je nach Verhalten des Benutzers eine lange Zeit in Anspruch nehmen kann. Bis der Benutzer reagiert und die Anwendung COMMIT ausgibt, ist das Abschneiden des Protokolls nicht möglich.
   3. Ein Anwendungsfehler, bei dem eine Transaktion nicht ausgeführt wird: Eine häufige Ursache ist die falsche Handhabung des DB-Library-Aufrufs dbcancel() innerhalb einer benutzerdefinierten Transaktion. Wenn eine Abfrage mit dbcancel() abgebrochen wird, dann wird die derzeit ausgeführte SQL-Anweisung abgebrochen und zurückgesetzt, nicht jedoch die äußere Transaktion. Die Anwendung muss dies berücksichtigen und die notwendige ROLLBACK TRANSACTION- oder COMMIT TRANSACTION-Anweisung ausgeben, um die Transaktion zu schließen. Andernfalls kann Fehler 3902 auftreten:
      Die Anforderung zur Übernahme der Transaktion besitzt kein entsprechendes BEGIN TRANSACTION.
      Unter Umständen ist es vorteilhaft, mit SELECT @@TRANCOUNT festzustellen, welche Verschachtelungstiefe der Transaktion besteht. Dies sollte jedoch von der Anwendung nicht blindlings vorgenommen werden und dann COMMIT/ROLLBACK ausgeführt werden, um @@TRANCOUNT=0 zu erreichen. Fällt @@TRANCOUNT anders als erwartet aus, ist dies ein Hinweis auf eine nicht mehr nachvollzogene Transaktionsverschachtelungsebene. Es handelt sich um einen Fehler im Aufbau der Anwendung. Die Anweisung COMMIT/ROLLBACK könnte an dieser Stelle dazu führen, dass unbeabsichtigte Transaktionen ausgeführt oder abgebrochen werden, da die Anwendung nicht erkennt, welche Transaktionen zu einer unbeabsichtigten Transaktionsebene geführt haben. Stattdessen sollte der Programmierer die Anwendung und alle betroffenen gespeicherten Prozeduren nach Fehlern durchsuchen, um die Ursache dieser unbeabsichtigten Transaktionsebene zu finden.
   4. Ein Netzwerkfehler, durch den SQL Server nicht vom Abbruch einer Netzwerkverbindung benachrichtigt wird: Wenn die Client-Arbeitsstation während einer benutzerdefinierten Transaktion nicht reagiert, neu gestartet oder heruntergefahren wird, sollte SQL Server im Normalfall von der Netzwerkschicht eine Benachrichtigung über dieses Ereignis erhalten. Ist dem nicht so, erscheint der Client aus der Sicht des SQL-Servers verfügbar, und die offene Transaktion dieses Clients wird aufrechterhalten. Hierbei handelt es sich um ein Netzwerkproblem, das auch als solches behandelt werden muss. Als Problemumgehung kann der Administrator jedoch mit Hilfe von sp_who, sp_lock oder eines Netzwerk-Dienstprogramms bestimmen, welche Clientsitzung noch geöffnet ist und diese dann manuell löschen.
   5. Transaktionen, die aufgrund von Blockierungen nicht ausgeführt werden: In einer Mehrbenutzerumgebung ist es möglich, dass eine offene Transaktion aufgrund von Sperren eines anderen Prozesses blockiert wird. In diesem Fall bleibt die Transaktion offen, und ein Abschneiden des Protokolls wird verhindert. Um dies zu erkennen, muss der Programmierer oder Datenbankadministrator sp_who, sp_lock oder andere Tools verwenden, um die konkurrierende Umgebung zu analysieren. In den meisten Fällen können Blockierungen durch genaues Strukturieren von Abfragen, Indizes und Datenbanken reduziert oder abgeschafft werden.
   6. Missglückter Versuch, eine Datenmodifizierungsabfrage zu beenden: Gibt die Anwendung dbcancel() aus und wird die Abfrage aufgrund eines Netzwerk- oder SQL-Problems nicht abgebrochen, läuft die Abfrage weiter und die Transaktion bleibt offen. Wenn Sie ein Problem vermuten, können Sie mit Hilfe von sp_who feststellen, ob die Abfrage abgebrochen wurde. Wenn versucht wurde, die Abfrage auf einem TCP/IP Sockets-Client abzubrechen, führen Sie diesen Test auf einem Named Pipes-Client durch, oder führen Sie die Clientanwendung auf dem Server-Computer aus, auf dem Local Pipes verwendet wird. Dadurch kann festgestellt werden, ob ein Netzwerk- oder ein SQL-Problem die Ursache dafür ist, dass die Abfrage nicht abgebrochen werden kann.
3. Die Abschneidebandbreite des Prüfpunkt-Handlers wurde überschritten: Obwohl das Protokoll alle 60 Sekunden abgeschnitten wird, ist die Rate, mit der dieser Abschneidevorgang stattfindet, endlich. Diese Situation ist selten, so dass Sie andere Gründe des Protokollüberlaufs erachten und kontrollieren sollten, bevor Sie diese Möglichkeit untersuchen. Es ist jedoch möglich, dass die maximale Abschneiderate überschritten wird, wenn viele Clients gleichzeitig umfangreiche Aktualisierungen vornehmen. (Dies gleicht einem Trichter, der Flüssigkeiten nur mit einer bestimmten Geschwindigkeit abfließen lässt und der währenddessen überfließen kann.) In dieser Situation können Sie die Anwendung umstrukturieren und die Anzahl der zu aktualisierenden Zeilen reduzieren, was stets das Hauptanliegen für das Design einer relationalen Datenbank sein sollte.
   
   Ist dies nicht durchführbar, kann das System mit einer erhöhten Festplatten-E/A-Bandbreite durch Striping, zusätzliche Controller usw. rekonfiguriert werden. In diesem Fall ist es üblich, dass der Prüfpunkt-Handlerprozess längere Zeit im DUMP TRANSACTION-Stadium zubringt, da er versucht, mit dem Abschneidevorgang des Protokolls Schritt zu halten. Ist die Abschneideschwelle überschritten (siehe unten), nimmt der Prüfpunkt-Handler in der Datenbank möglicherweise keine Entfernung der Einträge vor, es sei denn, das Protokoll wurde bereinigt.
4. Die Abschneideschwelle wurde überschritten: Der Prüfpunkt-Handler führt im Grunde DUMP TRANSACTION WITH TRUNCATE_ONLY aus. Wie bei manueller Eingabe ist diese Anweisung nicht immer erfolgreich, wenn das Protokoll bereits bis zu einem bestimmten Punkt voll ist. So kann eine umfangreiche Aktualisierungsaktivität das Protokoll zwischen zwei Betrachtungen des Prüfpunkt-Handlers bis zu 95% füllen. Versucht der Prüfpunkt-Handler, das Protokoll zu kürzen, kann es sein, dass das Protokoll - obwohl es noch nicht ganz belegt ist - zu voll ist, um eine Abschneidung zuzulassen. Die Ursache hierfür ist, dass auch das Abschneiden des Protokolls protokolliert werden muss. In diesem Fall liegt die einzige Lösung darin, das Protokoll mit Hilfe der Anweisung DUMP TRANSACTION WITH NO_LOG manuell abzuschneiden. Sie sollten die Option NO_LOG nur dann verwenden, wenn es absolut notwendig ist. Es handelt sich hierbei um eine nicht protokollierte Operation, während der ein Systemfehler zu Datenbankfehlern führen kann.
5. Zusammenspiel der zuvor genannten Situationen: Unter normalen Bedingungen verhindert die Abschneiderate des Prüfpunkt-Handlers in einer aktualisierungsintensiven Umgebung, dass das Protokoll aufgefüllt wird. Wenn eine vorübergehend offene Transaktion aufgrund einer der zuvor genannten Bedingungen (z. B. Sperrenkonflikt) dazu führt, dass das Protokoll bis zu beispielsweise 50% gefüllt ist, gibt es wesentlich weniger Handlungsspielraum für andere Aktualisierungen. Daher ist es wesentlich wahrscheinlicher, dass Sie die Abschneideschwelle erreichen, an der eine automatische Abschneidung nicht mehr möglich ist. Transaktionen in tempdb werden wie in jeder anderen Datenbank protokolliert. Da Protokoll bei Prüfpunkt abschneiden in tempdb aktiviert ist, wird das Protokoll in den meisten Fällen abgeschnitten und läuft nicht über. Jede der zuvor erwähnten Bedingungen kann jedoch dazu führen, dass das tempdb-Protokoll aufgefüllt wird. Normalerweise ist Tempdb für gemischte Protokolle und Daten konfiguriert (sysusages.segmap=7), so dass Daten- und Protokolloperationen um den gleichen zur Verfügung stehenden Speicherplatz konkurrieren. Einige Transact-SQL-Konstruktionen, wie z. B. GROUP BY, ORDER BY DESC usw., fordern bei tempdb automatisch Arbeitsspeicher an. Dies führt zu einem ausdrücklichen BEGIN TRANSACTION-Eintrag in tempdb, wenn Arbeitsspeicher angefordert wird. Die tempdb-Transaktion läuft während der gesamten Dauer der Transaktion in der Benutzerdatenbank, was das Abschneiden des tempdb-Protokolls in dieser Zeit verhindern kann. Wird die Transaktion in der Benutzerdatenbank aus irgendeinem Grund angehalten, z. B. wegen einer blockierenden Sperre, oder führt die Anwendung den Befehl dbnextrow() nicht vollständig aus, bleibt die Transaktion in tempdb auch offen und verhindert, dass das Protokoll in tempdb abgeschnitten wird. Der Programmierer muss die Anwendung dann auf Fehler untersuchen und/oder die konkurrierenden Datenbankzugriffe, die diese Situation hervorgerufen haben, anders gestalten.
6. Das Transaktionsprotokoll in SQL Server 7.0- und SQL Server 2000-Klassenserver wird abgeschnitten, indem die virtuellen Protokolldateien abgeschnitten werden. Wenn ein Teil des aktiven Protokolls in einer bestehenden virtuellen Protokolldatei gespeichert ist, kann diese virtuelle Protokolldatei nicht abgeschnitten werden. Wenn das aktive Protokoll auf allen virtuellen Protokolldateien gespeichert ist, kann das Protokoll nicht abgeschnitten werden. Wenn die automatische Vergrößerung aktiviert ist und das Volume, auf dem das Transaktionsprotokoll gespeichert ist, über ausreichenden Speicherplatz verfügt und die maximale Dateigröße noch nicht erreicht ist, wächst das Transaktionsprotokoll um die Größe an, die in den Eigenschaften der Protokolldatei angegeben ist.

Im Folgenden wird das Verhalten der Protokollabschneidung beim Starten von SQL, je nachdem, ob die Option Protokoll bei Prüfpunkt abschneiden aktiviert ist, erläutert.

• Wenn die Option Protokoll bei Prüfpunkt abschneiden aktiviert ist und ist das Protokoll zum Zeitpunkt des Startens voll ist, wird es automatisch mit NO_LOG gesichert.
• Protokoll bei Prüfpunkt abschneiden ist nun die Standardeinstellung in der master-Datenbank, da das Protokoll nicht auf einem separaten Medium gespeichert und somit nie geladen werden kann. Die einzig durchführbare Option ist das Löschen des Protokolls, wenn es voll ist.
• Ist Protokoll bei Prüfpunkt abschneiden deaktiviert und das Protokoll ist beim Starten voll, wird der Wiederherstellungsvorgang zwar beendet, doch der letzte Prüfpunkt wird nicht geschrieben. Der Administrator kann auf die Datenbank zugreifen und das Protokoll mit NO_TRUNCATE speichern, damit die Daten erhalten bleiben. Dann kann er mit NO_LOG sichern, um das Protokoll zu bereinigen (oder es nur bereinigen).

Informationsquellen

Weitere Informationen über SQL Server 2000 finden Sie in den folgenden Büchern:
Weitere Informationen finden Sie im folgendem Microsoft Trainings- &-Zertifizierungskurs: Weitere Informationen zu Problemen, die speziell SQL Server 7.0 und höher betreffen, finden Sie in folgendem Artikel der Microsoft Knowledge Base: