In diesem Beitrag wollen wir uns einmal genauer mit einem der wichtigsten Features von SQL-Server, dem Transaktionsprotokoll, beschäftigen. Wir werden uns ansehen, welche Wege wir gehen müssen, um dieses zu Verwalten und welche Aufgaben mit ihm bewerkstelligt werden können.
Bevor wir jedoch tiefer in die verschiedenen Anwendungsfälle eines Transaktionsprotokolls eintauchen, müssen wir vorerst die Frage klären, was sich hinter dem Namen Transaktionsprotokoll genau verbirgt.
Das Transaktionsprotokoll ist ein essenzieller Bestandteil von SQL Server. Jede einzelne Datenbank verfügt über ein Transaktionsprotokoll, welches in einer sogenannten Protokolldatei, getrennt von der eigentlichen Datenbankdatei, gespeichert wird.
Jede Datenbankänderung – und sei es nur einfaches INSERT – Statement, wird im Transaktionsprotokoll protokolliert. So ist es der Datenbank möglich, bei Auftreten eines Fehlers, jede durchgeführte Transaktion rückgängig zu machen oder sie wiederherzustellen. So können ernsthafte Datenbankbeschädigungen verhindert werden.
Einträge im Transaktionsprotokoll sind sequentiell. Das bedeutet, dass das Transaktionsprotokoll in kleine Abschnitte unterteilt ist, die wiederum als virtuelle Logdateien bezeichnet werden.
Ist die virtuelle Logdatei voll, werden alle darauffolgenden Transaktionen automatisch auf die nächste virtuelle Logdatei verschoben. Solange hierbei die Protokolldatensätze am Anfang des Transaktionsprotokolls abgeschnitten wurden, sobald die Protokollierung das Ende des Protokolls erreicht, wird dieses wieder zum Anfang zurückkehren und vorherige Einträge überschreiben:
Diese Frage ist sehr einfach zu beantworten, da eine Datenbank ohne ein Transaktionsprotokoll nicht korrekt funktionieren kann. Um diese Aussage nachvollziehen zu können, müssen wir allerdings erst einmal verstehen, was genau eigentlich eine Transaktion ist. Diese Frage werden wir im nächsten Abschnitt klären.
In diesem Abschnitt werden wir einzelne Funktionen betrachten, die ohne ein Transaktionsprotokoll nicht funktionieren könnten:
Unter einer Transaktion stellt man sich am besten eine in sich geschlossene Arbeitseinheit vor, die gegen eine Datenbank ausgeführt wird. Diese Arbeit kann manuell ausgeführt werden, beispielsweise durch ein UPDATE – Statement. Das Einfügen neuer Daten in die Datenbank stellt dann die Transaktion dar. SQL-Server unterstützt hier folgende Transaktionsmodi:
Die wichtigste Eigenschaft die eine Transaktion erfüllen muss, ist die sogenannte ACID-Eigenschaft. Diese besteht aus:
Um Transaktionen kontrolliert zu steuern, bietet SQL Server folgende Befehle:
Standardmäßig wird eine SQL-Anweisung wie beispielsweise ein SELECT– Statement sofort von SQL Server ausgeführt und das Ergebnis ausgegeben:
SELECT * FROM HumanResources.Employee
Fügen wir nun an den Anfang dieser Abfrage ein BEGIN TRANSACTION an, wird die Abfrage automatisch explizit und die Tabelle so lange gesperrt, bis die Transaktion entweder vollständig durchgelaufen ist oder zurückgesetzt wurde.
Durch das Anfügen eines BEGINN TRANSACTION an eine DELETE– oder UPDATE – Anweisung kann sichergestellt werden, dass diese auch wirklich korrekt ist und auch tatsächlich die richtige Anzahl von Ergebnissen zurückgegeben wird.
Um zu demonstrieren, wie hilfreich es sein kann eine DELETE– oder UPDATE – Anweisung in eine Transaktion zu verpacken, werden wir nun eine UPDATE– Anweisung durchführen, in welcher wir in jedem Eintrag bei jedem Eintrag mit der LoginID ‘%barbara%’ den Job Title auf ‘DBA’ setzen:
UPDATE HumanResources.Employee
SET JobTitle = ‘DBA’
WHERE LoginID IN (
SELECT LoginID FROM HumanResources.Employee)
Hoppla! Wir haben einen Fehler gemacht und vergessen in unserer Abfrage zu spezifizieren, dass wir nur die Einträge mit LoginID = ‘%barbara%’ aktualisieren wollen. Leider haben wir das erst nach dem Ausführen der Abfrage gemerkt und der Schaden ist angerichtet. Nun ist der Job Title für jeden Eintrag ‘DBA’:
Nachdem die Abfrage ausgeführt wurde, haben wir die Meldung erhalten, dass 290 Einträge von unserem UPDATE betroffen waren. Spätestens zu diesem Zeitpunkt ist uns bewusst, dass wir bei unserer Abfrage einen Fehler gemacht haben, jedoch wurde diese bereits durchgeführt und ist nicht mehr umkehrbar.
Wir führen das selbe Beispiel erneut durch, formulieren unsere Abfrage aber diesmal als eine Transaktion:
BEGIN TRAN
UPDATE HumanResources.Employee
SET JobTitle = 'DBA'
WHERE LoginID IN (
SELECT LoginID FROM HumanResources.Employee)
Wir erhalten erneut als Ausgabe, dass 290 Einträge betroffen sind:
Da wir aber dieses mal unsere Abfrage als eine Transaktion formuliert haben, wartet diese nun auf ein ROLLBACK oder COMMIT. So lange die Transaktion auf eine dieser beiden Anweisungen wartet, ist die betroffene Tabelle eingefroren und alle anderen Prozesse, die versuchen auf HumanResources.Employee zuzugreifen, werden blockiert.
Da uns aufgrund der 290 betroffen Einträge bewusst ist, dass bei unserer Abfrage ein Fehler passiert ist, können wir nun ein ROLLBACK durchführen und die Transaktion wieder rückgängig machen:
BEGIN TRAN
UPDATE HumanResources.Employee
SET JobTitle = 'DBA'
WHERE LoginID IN (
SELECT LoginID FROM HumanResources.Employee)
ROLLBACK TRAN
Und in der Tat wurde unser Fehler korrigiert und die Einträge wieder auf ihre ursprünglichen Werte zurückgesetzt:
Nun wollen wir unsere Abfrage einmal richtig formulieren und nur die Einträge aktualisieren, welche die Bedingung LoginID = ‘%barbara%’ erfüllen. Wir schreiben unsere Abfrage erneut als eine Transaktion:
BEGIN TRAN
UPDATE HumanResources.Employee
SET JobTitle = 'DBA'
WHERE LoginID IN (
SELECT LoginID FROM HumanResources.Employee WHERE LoginID LIKE '%barbara%')
Diesmal erhalten wir als Ausgabe, dass nur 2 Einträge betroffen sind. Unsere Abfrage scheint also erfolgreich gewesen zu sein und wir können diese nun mit:
COMMIT TRAN
freigeben.
Und tatsächlich haben wir unsere Tabelle erfolgreich und korrekt aktualisiert:
Innerhalb einer Transaktion kann ebenfalls wieder eine neue Transaktion definiert werden, was dann als eine verschachtelte Transaktion bezeichnet wird.
Diese verschachtelten Transaktionen erlauben es, Transaktionen in genau den gespeicherten Prozeduren zu unterstützen, die von einem Prozess aufgerufen werden können, der sich bereits in einer anderen Transaktion befindet.
Eine verschachtelte Transaktion könnte so aussehen:
BEGIN TRAN Tran1
GO
BEGIN TRAN Nested Tran
GO
INSERT INTO Table1 DEFAULT Values
GO 10
COMMIT TRAN Nested Tran
SELECT * FROM Table1
Würden wir die obige Transaktion Tran1 mit einem ROLLBACK zurücksetzen, so würde auch die Transaktion Nested Tran zurückgesetzt werden.
Mit der Zunahme von kritischen Geschäftssystemen in Verbindung mit SQL-Server erfreuen sich Hochverfügbarkeitslösungen immer größerer Beliebtheit. Hierbei findet das Transaktionsprotokoll sowohl bei Datenbankspiegelungen, Replikationen sowie des Protokollversands Anwendung.
Datenbankspiegelungen – Wird auf einer primären Datenbank eine DML-Anweisung ausgeführt, so muss diese auch so schnell wie möglich in der gespiegelten Datenbank erstellt werden. Dies wird durch das Senden eines kontinuierlichen Stroms aktiver Transaktionsprotokolldatensätze an den Spiegelserver realisiert. Der Spiegelserver wendet die eingehenden Protokolle der Reihe nach so schnell wie möglich auf die gespiegelte Datenbank an.
Bei einer asynchronen Spiegelung werden Transaktionen festgeschrieben, ohne dass darauf gewartet werden muss bis der Spiegelserver das Protokoll auf die Festplatte schreibt, was die Leistung maximiert. Wird eine synchrone Spiegelung verwendet, wird eine Transaktion auf beiden Partnern festgeschrieben, wodurch die Netzwerklatenz erhöht wird.
Was prinzipiell bei einer Datenbankspiegelung berücksichtigt werden sollte ist, dass im Falle einer angehaltenen Spiegelung weiterhin Datensätze im Transaktionsprotokoll angesammelt werden und die Protokolldatei nicht gekürzt werden kann. Bleibt eine Datenbankspiegelung eine längere Zeit im angehaltenen Zustand, kann dies zu einem überfüllten Protokoll führen.
Transaktionsreplikation – Die Transaktionsreplikation umfasst drei Hauptteile: Der SQL-Server-Snapshot-Agent, der Protokolllese-Agent und der Verteilungsagent.
Protokollversand – Im wesentlichen besteht der Protokollversand darin, ein Transaktionsprotokoll zu sichern, es auf einen anderen Server zu versenden und dieses dann wiederherzustellen. Bei Bedarf kann das Protokoll auch an mehrere sekundäre Server gesendet werden.
Wie bereits erwähnt, zeichnet das Transaktionsprotokoll alle Datenbankänderungen auf. Auf einem ausgelasteten System kann dies allein dazu führen, dass das Transaktionsprotokoll anwächst. Wenn die Funktion Autogrowth nicht aktiviert ist und die Protokolldatei die angegebene Maximalgröße erreicht, wird bei jeder Transaktion die die Datenbank trifft ein Fehler ausgegeben, bis das Problem behoben ist. Daher empfehlen wir Autogrowth immer zu aktivieren, das Transaktionsprotokoll aber im Auge zu behalten.
Weiterhin kann die Angabe eines kleinen Dateiwachstumsinkrements für die automatische Vergrößerung der Protokolldatei die Leistung beeinträchtigen. Darum sollte das Dateiwachstumsinkrement einer Protokolldatei groß genug gewählt sein, um eine häufige Erweiterung zu vermeiden. Der Standardwert ist hier 10% und dieser ist für die meisten Umgebungen ausreichend. Allerdings kann die optimale Einstellung je nach Umgebung variieren und die Verwendung eines festen Inkrements anstelle einer prozentualen Angabe für einige Umgebungen besser funktionieren. Der beste Weg ist hier, die Protokollnutzung zu überwachen und so nach und nach die Beste Einstellung für jede Datenbank herauszufinden.
Um Autogrowth zu aktivieren, können wir SSMS verwenden. Wir wählen die gewünschte Datenbank mit einem Rechtsklick aus und wählen die Eigenschaften. Unter den Datenbankeigenschaften können wir im Reiter Dateien die Autogrowth/Maxsize anpassen:
Andere Faktoren die für das Wachstum des Protokolls verantwortlich sein können sind:
Jede physische Transaktionsprotokolldatei ist intern in zahlreiche virtuelle Protokolldateien unterteilt. Diese haben keine bestimmte Größe und es ist nicht festgelegt, wie viele virtuelle Protokolldateien sich in einer physischen Protokolldatei befinden. Diese Aufgaben werden von der Datenbank-Engine übernommen, welche für eine bessere Performance stets versucht, die Anzahl virtueller Dateien möglichst klein zu halten.
Ist die virtuelle Protokolldatei durch kleine Größen- und growth_increment Werte definiert und wächst aufgrund vieler kleiner Inkremente auf eine große Größe an, wird die Anzahl der virtuellen Protokolldateien erhöht, was die Systemleistung beeinträchtigt. Aus diesem Grund sollte für Autogrowth eine größere Schrittweite konfiguriert werden. Ist das Protokoll beispielsweise so eingestellt, dass es nur um 1 MB wächst, kann es kontinuierlich wachsen, was zu immer mehr virtuellen Protokolldateien führt. Eine erhöhte Anzahl an virtuellen Protokolldateien kann den Datenbankstart verlangsamen.
Eine richtige oder falsche Anzahl an virtuellen Protokolldateien gibt es nicht, jedoch beeinträchtigt eine große Anzahl die Leistung. Um die Anzahl an virtuellen Protokolldateien zu prüfen, kann das Kommando
DBCC LOGINFO
verwendet werden.
SQL Server macht es glücklicherweise sehr einfach, Protokolldateien zu überwachen. Eine Möglichkeit dies zu tun, bietet die Katalogansicht sys.database_files. Diese Ansicht gibt Informationen zu Daten und Protokolldateien zurück, die unter Anderem Dateityp, Name, Speicherort, Status, Größe und Wachstum enthalten.
Mit folgender Abfrage kann nur nach der Protokolldatei gefiltert werden:
SELECT name AS [File Name],
physical_name AS [Physical Name],
size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB],
[growth], [file_id]
FROM sys.database_files
WHERE type_desc = 'LOG'
Weiterhin kann das Kommando
DBCC SQLPERF ('logspace')
ausgeführt werden, welches nützliche Details wie den DB-Namen, Protokollgröße (in MB) und den belegten Protokollspeicherplatz (in %) ausgibt:
Durch das Verkleinern des Protokolls wird Speicherplatz in der Protokolldatei freigegeben, sodass das Transaktionsprotokoll diesen wiederverwenden kann. Diese Protokollkürzung erfolgt automatisch nach einem Checkpoint (beim Wiederherstellungsmodell SIMPLE) oder nach einer Protokollsicherung (beim Wiederherstellungsmodell FULL).
Eine Protokollkürzung kann allerdings auch manuell auf zwei Arten gekürzt werden.
Um über SSMS eine Protokollkürzung vorzunehmen, wählen wir zunächst die gewünschte Datenbank mit einem Rechtsklick aus, wählen Aufgaben → Verkleinern → Datei:
Im darauffolgenden Dialogfenster wählen wir als Dateityp Protokoll aus. Weiterhin haben wir hier auch die Möglichkeit ungenutzten Speicherplatz freizugeben und Seiten neu zu organisieren.
Befindet sich die gewünschte Datenbank im Wiederherstellungsmodus SIMPLE, kann folgendes Kommando zum Kürzen des Protokolls genutzt werden:
DBCC SHRINKFILE (logfile_name, 1)
Die Argumente sind hier der Name des gewünschten Logfiles sowie die Anzahl an MB um welche diese gekürzt werden soll.
Ist der Wiederherstellungsmodus FULL, so kann dieser temporär auf FULL gesetzt werden:
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks2012_log, 1)
GO
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL
Um den logischen Namen einer Protokolldatei zu finden, kann folgende Abfrage genutzt werden:
SELECT name FROM sys.master_files WHERE type_desc = 'LOG'
Eine weitere Möglichkeit das Protokoll zu verkürzen, während sich die Datenbank im Wiederherstellungsmodell Full befindet, besteht darin, das Protokoll mit dem Kommando BACKUP LOG und anschließend mit dem Befehl SHRINKFILE auszugeben:
Eine weitere Möglichkeit das Protokoll zu verkürzen, während sich die Datenbank im Wiederherstellungsmodell Full befindet, besteht darin, das Protokoll mit dem Kommando BACKUP LOG und anschließend mit dem Befehl SHRINKFILE auszugeben:
Full – Beim vollständigen Wiederherstellungsmodell sollten Protokolldateien regelmäßig gesichert werden, um das Überfüllen des Laufwerks zu verhindern. Weiterhin kann im vollständigen Wiederherstellungsmodell abhängig von den Protokollsicherungszeitplänen eine Datenbank bis zum Zeitpunkt des Ausfalls wiederhergestellt werden.
Simple – Im einfachen Wiederherstellungsmodell können Transaktionsprotokollsicherungen nicht verwendet werden. Dies bedeutet, dass SQL Server in bestimmten Intervallen automatisch Speicherplatz zurückfordert. Im Falle eines Fehlers kann aber nur die letzte vollständige Sicherung wiederhergestellt werden, da alle Transaktionen zwischendurch verloren gehen.
Um das Wiederherstellungsmodell einer Datenbank anzupassen, kann SSMS verwendet werden. Hierfür einfach die gewünschte Datenbank mit einem Rechtsklick auswählen und anschließend Eigenschaften wählen. Unter dem Reiter Optionen kann das Wiederherstellungsmodell gesetzt werden:
Neben SSMS kann das Setzen des Wiederherstellungsmodells auch mithilfe von T-SQL erfolgen. Hierfür kann folgendes Kommando ausgeführt werden:
Use [master]
GO
ALTER DATABASE [AdventureWorks2012] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Wenn wir ein Transaktionsprotokoll aus einer Sicherung wiederherstellen möchten, müssen wir bis auf das letzte Protokoll alle anderen mit der Bedingung WITH NORECOVERY wiederherstellen. Erst das letzte Protokoll muss mit der Bedingung WITH RECOVERY wiederhergestellt werden.
Das Wiederherstellen des Transaktionsprotokolls ist nicht möglich, wenn
Das folgende Beispiel beginnt mit der Wiederherstellung einer vollständigen Sicherung, gefolgt von zwei Transaktionsprotokollsicherungen:
RESTORE DATABASE AdventureWorks2012
FROM BackupDevice
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2012
FROM BackupDevice
WITH FILE = 1, NORECOVERY
GO
RESTORE LOG AdventureWorks2012
FROM BackupDevice
WITH FILE = 2, RECOVERY
GO
In diesem Beitrag haben Sie Transaktionen, sowie das Transaktionsprotokoll kennengelernt. Wir haben gesehen wie nützlich es sein kann, SQL Abfragen als Transaktionen zu formulieren, um so jederzeit die Möglichkeit zu haben, im Falle eines Fehlers die gesamte Transaktion rückgängig zu machen. Weiterhin haben wir uns das Wachstumsverhalten der Protokolldatei und Wege angesehen, wie wir dieses kontrollieren und überwachen können.
Kontaktieren Sie uns gerne über das
Kontaktformular und vereinbaren ein unverbindliches
Beratungsgespräch mit unseren Berater:innen zur
Bedarfsevaluierung. Gemeinsam optimieren wir Ihre
Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!