Version Store und Transaktionslog: Was eine offene Transaktion festhält

Diese Transaktion arbeitet nicht. Sie hält aber fest, was andere erzeugen: Zeilenversionen, die sie noch sehen könnte, und Protokoll, das ab ihrem Beginn nicht wiederverwendet werden darf. Wo das landet, hängt davon ab, ob die beschleunigte Datenbankwiederherstellung (Accelerated Database Recovery, ADR) eingeschaltet ist.

Wo Zeilenversionen liegen

SQL Server legt Zeilenversionen an, wenn eine Datenbank Read Committed Snapshot (RCSI) oder Snapshot Isolation nutzt. Auch Trigger, Online-Indexoperationen und MARS erzeugen Versionen. Gespeichert werden sie je nach Konfiguration an unterschiedlichen Orten:

Ab SQL Server 2025 kann ADR auch für TempDB selbst eingeschaltet werden. Das haben wir nicht getestet.

Was Versionen festhält

Versionen bleiben so lange, wie eine Transaktion sie noch lesen könnte. Bei Snapshot Isolation ist das die älteste offene Snapshot-Transaktion, bei RCSI die am längsten laufende Anweisung. Schreibt die übrige Anwendung währenddessen weiter, wächst der Version Store mit jeder Änderung.

Im Lab las eine Sitzung in SNAPSHOT-Isolation einmal Daten und ließ ihre Transaktion danach 150 Sekunden offen, ohne selbst etwas zu ändern. Währenddessen änderte eine zweite Sitzung zehnmal alle 100.000 Zeilen einer Tabelle, jede Runde mit Commit.

Die Zuordnung ist eindeutig: Ohne ADR landeten alle Versionen in TempDB, mit ADR keine einzige. Solange die lesende Transaktion offen war, blieb alles liegen, obwohl der Aufräumvorgang mehrfach lief. Nach ihrem Ende war TempDB nach 17 bis 20 Sekunden leer. Der PVS-Cleaner arbeitet in einem festen Takt von einer Minute und gab beim ersten Durchlauf danach alles frei.

Wichtig für die Diagnose: TempDB-Platz für Versionen wird nicht der haltenden Sitzung zugerechnet. Die Sitzung, die alles festhält, hat oft selbst keine einzige Seite in TempDB belegt. Sie finden sie über die offenen Snapshot-Transaktionen und deren Alter, nicht über den Platzverbrauch je Sitzung.

Was das Protokoll festhält

Im Wiederherstellungsmodell SIMPLE gibt ein Checkpoint den Protokollbereich frei, der nicht mehr gebraucht wird. Ab dem Beginn der ältesten offenen Transaktion wird aber nichts frei: SQL Server könnte diese Transaktion noch zurückrollen müssen. sys.databases zeigt dann als Wartegrund ACTIVE_TRANSACTION. Das gilt für Benutzerdatenbanken genauso wie für TempDB selbst.

Im Lab hielt eine Transaktion eine einzelne Zeile geändert offen. Eine zweite Sitzung änderte 60-mal je 5.000 Zeilen, committete jedes Mal und führte jeweils einen Checkpoint aus:

Ohne ADR blieb das Protokoll bis zum Ende der Messung voll belegt. Mit ADR wurde es ab rund 39 Sekunden schrittweise wieder frei und lag zum Schluss bei 3,1 MB, obwohl die Transaktion noch offen war. ADR braucht das Protokoll nicht mehr, um diese Transaktion zurückzurollen, weil die alten Versionen im PVS liegen. Den Effekt sieht man erst nach einer Weile: In einem kürzeren Versuch mit zwölf Runden stand der Wartegrund in beiden Varianten noch auf ACTIVE_TRANSACTION.

Rollback: schnell zurück, aufgeräumt wird später

ADR verändert auch das Zurückrollen. Nach 100.000 geänderten Zeilen kehrte ROLLBACK im Lab ohne ADR im Median nach 303 ms zurück, mit ADR nach 0,7 ms. SQL Server muss die alten Werte nicht zurückschreiben, sie liegen bereits im PVS. Das physische Aufräumen erledigt der PVS-Cleaner später im Hintergrund.

ADR ist kein Schalter ohne Preis. Die Versionen belegen Platz in der Benutzerdatenbank, und das Schreiben der Versionen kostet Arbeit. Im Version-Store-Versuch belegte das Protokoll mit ADR 491 statt 142 MB, der Schreiber brauchte 5 bis 6 statt 2 bis 3 Sekunden, und die Tabelle hatte danach doppelt so viele, nur halb gefüllte Seiten, auch nachdem der PVS wieder leer war. Woher die Verdopplung genau kommt, haben wir nicht untersucht. In unserem Lab mit konkurrierenden Schreibern, die Tabellen absichtlich ohne passenden Index durchsuchten, sank der Durchsatz eines einzelnen Schreibers mit ADR allein von 68 auf 53 Änderungen pro Sekunde, die CPU-Zeit des Containers je Änderung stieg auf fast das Dreifache. Erst zusammen mit RCSI und Optimized Locking (SQL Server 2025) kehrte sich das bei acht gleichzeitigen Schreibern um. Das ist ein bewusst zugespitzter Fall, keine allgemeine Quote. Er zeigt aber: Vor dem Einschalten gehört ADR mit der eigenen Last getestet.

So finden Sie den Halter

Die folgende Abfrage zeigt für alle Datenbanken der Instanz, welche Versionierung aktiv ist, warum das Protokoll nicht frei wird, wie groß der PVS ist (ab SQL Server 2019) und welche Transaktionen Versionen und Protokoll am längsten festhalten. Für TempDB selbst nutzen Sie das Lagebild auf der Übersichtsseite.

/* PSG Stack · SQL Server TempDB · Abfrage 2
   Wer hält Zeilenversionen und Protokoll fest? Alle Datenbanken der Instanz.

   - Nur lesend. Ändert nichts, beendet keine Sitzung.
   - Liefert vier Ergebnisse:
       1 Datenbanken: Isolationsoptionen, ADR, Protokoll-Wartegrund
       2 Älteste offene Transaktionen mit Protokoll je Datenbank
       3 Persistent Version Store (nur ab SQL Server 2019 mit ADR)
       4 Älteste Snapshot-Transaktionen, auch reine Leser
   - Rechte: VIEW SERVER STATE
     (ab SQL Server 2022 genügt VIEW SERVER PERFORMANCE STATE).
     Ohne dieses Recht liefert Ergebnis 1 trotzdem Zeilen, Ergebnis 2 scheitert mit Fehler 300
     und Ergebnis 3 mit Fehler 262 (Ergebnis 3 genügt auch VIEW DATABASE STATE).
   - Ab SQL Server 2017. Geprüft im PSG-Lab auf 2017, 2019, 2022 und 2025.
     Ergebnis 3 entfällt auf SQL Server 2017.
*/

/* 1 · Datenbanken: RCSI, Snapshot Isolation, ADR und Wartegrund des Protokolls */
DECLARE @adr nvarchar(100) =
    CASE WHEN COL_LENGTH('sys.databases', 'is_accelerated_database_recovery_on') IS NOT NULL
         THEN N'd.is_accelerated_database_recovery_on' ELSE N'CAST(NULL AS bit)' END;
DECLARE @sql nvarchar(max) = N'
SELECT
    d.name                                   AS datenbank,
    d.is_read_committed_snapshot_on          AS rcsi,
    d.snapshot_isolation_state_desc          AS snapshot_isolation,
    ' + @adr + N'                            AS adr,
    d.recovery_model_desc                    AS wiederherstellungsmodell,
    d.log_reuse_wait_desc                    AS protokoll_wartegrund
FROM sys.databases AS d
WHERE d.state = 0
ORDER BY d.name
OPTION (MAXDOP 1);';
EXEC sys.sp_executesql @sql;

/* 2 · Älteste offene Transaktionen mit Protokoll je Datenbank
   Reine Leser in SNAPSHOT-Isolation erscheinen hier nicht: Sie haben kein Protokoll geschrieben
   (database_transaction_begin_time ist NULL). Sie zeigt Ergebnis 4. */
SELECT TOP (20)
    DB_NAME(dt.database_id)                                               AS datenbank,
    st.session_id,
    dt.database_transaction_begin_time                                    AS begonnen,
    DATEDIFF(SECOND, dt.database_transaction_begin_time, SYSDATETIME())   AS sekunden_offen,
    CAST(dt.database_transaction_log_bytes_used / 1048576.0 AS decimal(12,1)) AS protokoll_genutzt_mb,
    es.status,
    es.login_name,
    es.program_name,
    es.last_request_end_time                                              AS letzte_anfrage_beendet
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = dt.transaction_id
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = st.session_id
WHERE dt.database_transaction_begin_time IS NOT NULL
ORDER BY dt.database_transaction_begin_time
OPTION (MAXDOP 1);

/* 3 · Persistent Version Store (ADR), ab SQL Server 2019
   Wer die Versionen festhält, zeigen Ergebnis 2 (schreibende Transaktionen) und Ergebnis 4 (Snapshot-Transaktionen). */
IF OBJECT_ID('sys.dm_tran_persistent_version_store_stats') IS NOT NULL
    EXEC sys.sp_executesql N'
    SELECT
        DB_NAME(pvs.database_id)                                      AS datenbank,
        CAST(pvs.persistent_version_store_size_kb / 1024.0 AS decimal(12,1)) AS pvs_mb
    FROM sys.dm_tran_persistent_version_store_stats AS pvs
    WHERE pvs.persistent_version_store_size_kb > 0
    ORDER BY pvs.persistent_version_store_size_kb DESC
    OPTION (MAXDOP 1);';

/* 4 · Älteste Snapshot-Transaktionen: Sie halten Versionen in TempDB und im PVS fest,
   auch wenn sie nur lesen. snapshot_isolation = 0: Transaktion ohne SNAPSHOT-Isolation,
   die Zeilenversionen erzeugt (z. B. Schreiber unter READ_COMMITTED_SNAPSHOT). */
SELECT TOP (10)
    ast.session_id,
    ast.elapsed_time_seconds                      AS sekunden_offen,
    ast.is_snapshot                               AS snapshot_isolation,
    es.login_name,
    es.program_name,
    es.status,
    es.last_request_end_time                      AS letzte_anfrage_beendet
FROM sys.dm_tran_active_snapshot_database_transactions AS ast
LEFT JOIN sys.dm_exec_sessions AS es ON es.session_id = ast.session_id
ORDER BY ast.elapsed_time_seconds DESC
OPTION (MAXDOP 1);

So lesen Sie das Ergebnis:

• protokoll_wartegrund = ACTIVE_TRANSACTION: In Ergebnis 2 steht die zugehörige Transaktion weit oben. Ist ihr status „sleeping“ und letzte_anfrage_beendet lange her, wartet niemand auf diese Transaktion; sie wurde schlicht nicht beendet.
• Versionen wachsen, in TempDB oder im PVS: Den Halter zeigt Ergebnis 4, die älteste Snapshot-Transaktion, auch wenn sie nur liest. In Ergebnis 2 erscheint ein reiner Leser nicht, weil er kein Protokoll geschrieben hat. Im Lab war er nur in Ergebnis 4 zu sehen.
• pvs_mb sinkt nicht mehr: Der PVS-Cleaner kann nicht aufräumen, solange Ergebnis 2 oder 4 eine alte Transaktion zeigt. Ein großer Wert allein belegt aber noch keine festgehaltenen Versionen: In einem Lauf stieg pvs_mb nach dem Aufräumen ohne weitere Änderungen auf rund 1 GB, offenbar vorab reservierter Platz. Die Ursache haben wir nicht geklärt.

Was dauerhaft hilft

• Transaktionen kurz halten. Keine Benutzereingaben, Dateizugriffe oder externen Aufrufe innerhalb einer offenen Transaktion. Anwendungen, die Verbindungen poolen, sollten Transaktionen zuverlässig abschließen, auch im Fehlerfall (SET XACT_ABORT ON).
• Lange Leser bewusst einsetzen. Ein Bericht in SNAPSHOT-Isolation, der eine Stunde läuft, hält eine Stunde lang Versionen fest. Läuft er auf einer lesbaren Kopie, verlagert sich das Problem; es verschwindet nicht.
• Überwachen, was festhält. Ein Alarm auf alte offene Transaktionen und auf log_reuse_wait_desc meldet das Problem, bevor TempDB oder das Protokoll voll ist.
• ADR gezielt prüfen. ADR verlagert Versionen aus TempDB in die Datenbank, verkürzt Rollbacks und lässt das Protokoll trotz offener Transaktion frei werden. Dafür kostet es Platz und Schreibarbeit in der Datenbank. Testen Sie es mit Ihrer Last, bevor Sie es einschalten.
• TempDB ausreichend groß planen. Das verschafft Zeit, ersetzt aber nicht das Beenden der haltenden Transaktion.

Quellen

• Microsoft: Transaktionssperren und Zeilenversionierung
• Microsoft: Accelerated Database Recovery: Konzepte und Problembehandlung
• Microsoft: Das Transaktionsprotokoll und Gründe für verzögertes Kürzen
• Microsoft: Die TempDB-Datenbank
• Microsoft: sys.dm_tran_persistent_version_store_stats

Weitere TempDB-Themen