Freigeben über

Problembehandlung bei vollen Transaktionsprotokollen (SQL Server-Fehler 9002)

Gilt für:SQL Server

Dieser Artikel bezieht sich auf SQL Server-Instanzen.

Hinweis

Dieser Artikel ist auf SQL Server ausgelegt. Spezifischere Informationen zu diesem Fehler auf Azure SQL-Plattformen finden Sie unter Problembehandlung von Transaktionsprotokollfehlern mit Azure SQL-Datenbank und Problembehandlung von Transaktionsprotokollfehlern mit Azure SQL Managed Instance. Azure SQL-Datenbank und Azure SQL Managed Instance basieren auf der letzten stabilen Version der Microsoft SQL Server-Datenbank-Engine, weshalb sich die Inhalte größtenteils ähneln. Allerdings kann es Unterschiede bei den Optionen und Tools für die Problembehandlung geben.

Option 1: Führen Sie die Schritte direkt in einem ausführbaren Notizbuch über Azure Data Studio aus.

Bevor Sie versuchen, dieses Notebook zu öffnen, stellen Sie sicher, dass Azure Data Studio auf Ihrem lokalen Computer installiert ist. Um zu installieren, rufen Sie die Seite Herunterladen und Installieren von Azure Data Studio auf.

Notebook in Azure Data Studio öffnen

Option 2: Führen Sie die Schritte manuell aus.

In diesem Artikel werden mögliche Lösungen für volle Transaktionsprotokolle erörtert und Vermeidungsstrategien vorgeschlagen.

Wenn das Transaktionsprotokoll voll wird, gibt das SQL Server-Datenbankmodul einen Fehler 9002 aus. Das Protokoll kann sich füllen, wenn die Datenbank online ist oder wiederhergestellt wird. Falls das Protokoll während des Onlinezustands der Datenbank voll ist, bleibt die Datenbank online, aber sie kann nur gelesen und nicht aktualisiert werden. Wenn das Protokoll während der Wiederherstellung gefüllt wird, markiert das Datenbankmodul die Datenbank als RESOURCE PENDING. In beiden Fällen ist eine Aktion seitens des Benutzers erforderlich, um Speicherplatz im Protokoll verfügbar zu machen.

Häufige Gründe für ein volles Transaktionsprotokoll

Die richtige Reaktion auf ein volles Transaktionsprotokoll hängt davon ab, aufgrund welcher Bedingungen das Protokoll gefüllt wurde. Häufige Ursachen sind:

  • Protokoll wird nicht gekürzt
  • Datenträgervolume ist voll
  • Protokollgröße ist auf einen festen Höchstwert festgelegt oder automatische Vergrößerung ist deaktiviert
  • Replikation oder Synchronisierung von Verfügbarkeitsgruppen kann nicht abgeschlossen werden

Führen Sie die folgenden spezifischen Schritte aus, um den Grund für ein vollständiges Transaktionsprotokoll zu finden und das Problem zu beheben.

1. Kürzen des Protokolls

Eine häufige Lösung für dieses Problem besteht darin, sicherzustellen, dass Transaktionsprotokoll-Backups für Ihre Datenbank ausgeführt werden, damit das Protokoll verkürzt wird. Ist kein aktueller Transaktionsprotokollverlauf für die Datenbank mit einem vollen Transaktionsprotokoll angegeben, ist die Lösung des Problems einfach: Setzen Sie die regelmäßigen Transaktionsprotokollsicherungen der Datenbank fort.

Weitere Informationen hierzu können Sie unter "Verwalten der Größe der Transaktionsprotokolldatei " und " Verkleinern einer Datei" lesen.

Erläuterung zur Protokollkürzung

Es gibt einen Unterschied zwischen dem Kürzen eines Transaktionsprotokolls und dem Verkleinern eines Transaktionsprotokolls. Das Kürzen von Protokollen erfolgt normalerweise während einer Transaktionsprotokollsicherung und ist ein logischer Vorgang, bei dem Datensätze, für die ein Commit ausgeführt wurde, aus dem Protokoll entfernt werden, während das Verkleinern von Protokollen physischen Speicherplatz im Dateisystem freigibt, indem die Dateigröße reduziert wird. Das Kürzen von Protokollen erfolgt an einer Virtual Log File-Grenze (VLF), und eine Protokolldatei kann viele VLFs enthalten. Eine Protokolldatei kann nur verkleinert werden, wenn sie leeren Speicherplatz enthält, der freigegeben werden kann. Die Verkleinerung einer Protokolldatei allein kann das Problem einer vollen Protokolldatei nicht lösen. Stattdessen müssen Sie ermitteln, warum die Protokolldatei voll ist und nicht abgeschnitten werden kann.

Warnung

Die zum Verkleinern einer Datei verschobenen Daten können an beliebigen freien Platz in der Datei verschoben werden. Dies führt zur Indexfragmentierung und kann die Leistung von Abfragen, die einen Bereich des Indexes suchen, verlangsamen. Zur Vermeidung von Fragmentierung sollten die Dateiindizes nach der Verkleinerung neu erstellt werden. Weitere Informationen finden Sie unter Verkleinern einer Datenbank.

Was verhindert eine Protokollkürzung?

Verwenden Sie die Spalten log_reuse_wait und log_reuse_wait_desc der sys.databases-Katalogansicht, um zu ermitteln, was die Protokollkürzung in einem bestimmten Fall verhindert. Weitere Informationen finden Sie unter sys.databases. Eine Beschreibung von Faktoren, die eine Protokollkürzung verzögern können, finden Sie unter Das Transaktionsprotokoll.

Die folgenden T-SQL-Befehle helfen Ihnen zu ermitteln, ob ein Datenbanktransaktionsprotokoll nicht gekürzt wurde, und nennt den Grund dafür. Das folgende Skript empfiehlt außerdem Schritte zur Behebung des Problems:

SET NOCOUNT ON;

DECLARE
    @SQL AS VARCHAR (8000),
    @log_reuse_wait AS TINYINT,
    @log_reuse_wait_desc AS NVARCHAR (120),
    @dbname AS SYSNAME,
    @database_id AS INT,
    @recovery_model_desc AS VARCHAR (24);

IF (OBJECT_id(N'tempdb..#CannotTruncateLog_Db') IS NOT NULL)
    BEGIN
        DROP TABLE #CannotTruncateLog_Db;
    END

--get info about transaction logs in each database.
IF (OBJECT_id(N'tempdb..#dm_db_log_space_usage') IS NOT NULL)
    BEGIN
        DROP TABLE #dm_db_log_space_usage;
    END

SELECT *
INTO #dm_db_log_space_usage
FROM sys.dm_db_log_space_usage
WHERE 1 = 0;

DECLARE log_space CURSOR
    FOR SELECT NAME
        FROM sys.databases;

OPEN log_space;

FETCH NEXT FROM log_space INTO @dbname;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = '
            INSERT INTO #dm_db_log_space_usage (
                database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
                )
            SELECT database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
            FROM ' + QUOTENAME(@dbname) + '.sys.dm_db_log_space_usage;';
        BEGIN TRY
            EXECUTE (@SQL);
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
        END CATCH
        FETCH NEXT FROM log_space INTO @dbname;
    END

CLOSE log_space;
DEALLOCATE log_space;

--select the affected databases
SELECT
    sdb.name AS DbName,
    sdb.log_reuse_wait,
    sdb.log_reuse_wait_desc,
    CASE
        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a deferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
        ELSE 'None'
    END AS log_reuse_wait_explanation,
    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024. / 1024. AS Used_log_size_MB,
    lsu.total_log_size_in_bytes / 1024. / 1024. AS Total_log_size_MB,
    100 - lsu.used_log_space_in_percent AS Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb
     INNER JOIN #dm_db_log_space_usage AS lsu
         ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0;

SELECT * FROM #CannotTruncateLog_Db;

DECLARE no_truncate_db CURSOR FOR
SELECT
    log_reuse_wait,
    log_reuse_wait_desc,
    DbName,
    database_id,
    recovery_model_desc
FROM #CannotTruncateLog_Db;

OPEN no_truncate_db;

FETCH NEXT FROM no_truncate_db
INTO
    @log_reuse_wait,
    @log_reuse_wait_desc,
    @dbname,
    @database_id,
    @recovery_model_desc;

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@log_reuse_wait > 0)
        BEGIN
            SELECT '-- ' + QUOTENAME(@dbname) + ' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --' AS 'Individual Database Report';
        END
        IF (@log_reuse_wait = 1)
        BEGIN
            SELECT 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' AS Recommendation;
            SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT' AS CheckpointCommand;
            SELECT 'SELECT * FROM sys.dm_db_log_info(' + CONVERT (VARCHAR, @database_id) + ')' AS VLF_LogInfo;
        END
        ELSE IF (@log_reuse_wait = 2)
        BEGIN
            SELECT 'Is ' + @recovery_model_desc + ' recovery model the intended choice for ' + QUOTENAME(@dbname) + ' database? Review recovery models and determine if you need to change it. https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server' AS RecoveryModelChoice;
            SELECT 'To truncate the log consider performing a transaction log backup on database ' + QUOTENAME(@dbname) + ' which is in ' + @recovery_model_desc + ' recovery model. Be mindful of any existing log backup chains that could be broken' AS Recommendation;
            SELECT 'BACKUP LOG ' + QUOTENAME(@dbname) + ' TO DISK = ''some_volume:\some_folder\' + QUOTENAME(@dbname) + '_LOG.trn '';' AS BackupLogCommand;
        END
        ELSE IF (@log_reuse_wait = 3)
        BEGIN
            SELECT 'Either wait for or cancel any active backups currently running for database ' + QUOTENAME(@dbname) + '. To check for backups, run this command:' AS Recommendation;
            SELECT 'SELECT * FROM sys.dm_exec_requests WHERE command LIKE ''backup%'' OR command LIKE ''restore%''' AS FindBackupOrRestore;
        END
        ELSE IF (@log_reuse_wait = 4)
        BEGIN
            SELECT 'Active transactions currently running for database ' + QUOTENAME(@dbname) + '. To check for active transactions, run these commands:' AS Recommendation;
            SELECT 'DBCC OPENTRAN (' + QUOTENAME(@dbname) + ')' AS FindOpenTran;
            SELECT 'SELECT database_id, db_name(database_id) AS dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id FROM sys.dm_tran_database_transactions dbtran LEFT OUTER JOIN sys.dm_tran_session_transactions stran ON dbtran.transaction_id = stran.transaction_id WHERE database_id = ' + CONVERT (VARCHAR, @database_id) AS FindOpenTransAndSession;
        END
        ELSE IF (@log_reuse_wait = 5)
        BEGIN
            SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' is behind on synchronization. To check the state of DBM, run the commands below:' AS Recommendation;
            SELECT 'SELECT db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL and mirroring_state <> 4 AND database_id = ' + CONVERT (sysname, @database_id) AS CheckMirroringStatus;
            SELECT 'Database Mirroring for database ' + QUOTENAME(@dbname) + ' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' AS Recommendation;
            SELECT 'EXECUTE msdb.sys.sp_dbmmonitoraddmonitoring 1; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + ', 5, 0; WAITFOR DELAY ''00:01:01''; EXECUTE msdb.sys.sp_dbmmonitorresults ' + QUOTENAME(@dbname) + '; EXECUTE msdb.sys.sp_dbmmonitordropmonitoring' AS CheckMirroringStatusAnd;
        END
        ELSE IF (@log_reuse_wait = 6)
        BEGIN
            SELECT 'Replication transactions still undelivered FROM publisher database ' + QUOTENAME(@dbname) + ' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encountered any errors' AS Recommendation;
            SELECT 'DBCC OPENTRAN  (' + QUOTENAME(@dbname) + ')' AS CheckOldestNonDistributedTran;
            SELECT 'SELECT top 5 * FROM distribution..MSlogreader_history WHERE runstatus in (6, 5) OR error_id <> 0 AND agent_id = find_in_mslogreader_agents_table ORDER BY time desc ' AS LogReaderAgentState;
        END
        ELSE IF (@log_reuse_wait = 9)
        BEGIN
            SELECT 'Always On transactions still undelivered FROM primary database ' + QUOTENAME(@dbname) + ' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' AS Recommendation;
            SELECT 'SELECT availability_group = CAST(ag.name AS VARCHAR(30)), primary_replica = CAST(ags.primary_replica AS VARCHAR(30)), primary_recovery_health_desc = CAST(ags.primary_recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ags.synchronization_health_desc AS VARCHAR(30)), ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc = CAST(ag.automated_backup_preference_desc AS VARCHAR(10)) FROM sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' AS CheckAGHealth;
            SELECT 'SELECT  group_name = CAST(arc.group_name AS VARCHAR(30)), replica_server_name = CAST(arc.replica_server_name AS VARCHAR(30)), node_name = CAST(arc.node_name AS VARCHAR(30)), role_desc = CAST(ars.role_desc AS VARCHAR(30)), ar.availability_mode_Desc, operational_state_desc = CAST(ars.operational_state_desc AS VARCHAR(30)), connected_state_desc = CAST(ars.connected_state_desc AS VARCHAR(30)), recovery_health_desc = CAST(ars.recovery_health_desc AS VARCHAR(30)), synchronization_health_desc = CAST(ars.synchronization_health_desc AS VARCHAR(30)), ars.last_connect_error_number, last_connect_error_description = CAST(ars.last_connect_error_description AS VARCHAR(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc = CAST(ar.primary_role_allow_connections_desc AS VARCHAR(30)) FROM sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY CAST(arc.group_name AS VARCHAR(30)), CAST(ars.role_desc AS VARCHAR(30))' AS CheckReplicaHealth;
            SELECT 'SELECT database_name = CAST(drcs.database_name AS VARCHAR(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local, drcs.is_failover_ready, drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc = CAST(drs.suspend_reason_desc AS VARCHAR(30)), synchronization_state_desc = CAST(drs.synchronization_state_desc AS VARCHAR(30)), synchronization_health_desc = CAST(drs.synchronization_health_desc AS VARCHAR(30)), database_state_desc = CAST(drs.database_state_desc AS VARCHAR(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time FROM sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id  order by drs.database_id' AS LogMovementHealth;
            SELECT 'For more information see https://learn.microsoft.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' AS OnlineDOCResource;
        END
        ELSE IF (@log_reuse_wait IN (10, 11, 12, 14))
        BEGIN
            SELECT 'This state is not documented and is expected to be rare and short-lived' AS Recommendation;
        END
        ELSE IF (@log_reuse_wait = 13)
        BEGIN
            SELECT 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' AS Finding;
            SELECT 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' AS Recommendation;
            SELECT 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET TARGET_RECOVERY_TIME = 0 SECONDS;' AS DisableIndirectCheckpointTemporarily;
        END
        ELSE IF (@log_reuse_wait = 16)
        BEGIN
            SELECT 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' AS Finding;
            SELECT 'Review https://learn.microsoft.com/archive/blogs/sqlcat/logging-and-checkpoint-process-for-memory-optimized-tables-2' AS ReviewBlog;
            SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT;' AS RunCheckpoint;
        END
        FETCH NEXT FROM no_truncate_db INTO
            @log_reuse_wait,
            @log_reuse_wait_desc,
            @dbname,
            @database_id,
            @recovery_model_desc;
    END

CLOSE no_truncate_db;
DEALLOCATE no_truncate_db;

Wichtig

Wenn die Datenbank beim Auftreten des Fehlers 9002 wiederhergestellt wurde, stellen Sie die Datenbank nach dem Beheben des Problems mithilfe von ALTER DATABASE database_name SET ONLINE wieder her.

LOG_BACKUP log_reuse_wait

Die häufigste Aktion, die in Betracht gezogen werden sollte, wenn Sie LOG_BACKUP oder log_reuse_wait sehen, ist, Ihr Datenbankwiederherstellungsmodell zu überprüfen und das Transaktionsprotokoll Ihrer Datenbank zu sichern.

Überprüfen des Datenbankwiederherstellungsmodells

Das Transaktionsprotokoll kann möglicherweise mit den Kategorien LOG_BACKUP oder log_reuse_wait nicht gekürzt werden, da Sie es noch nie gesichert haben. In vielen dieser Fälle verwendet Ihre Datenbank das FULL- oder BULK_LOGGED-Wiederherstellungsmodell, aber Sie haben Ihr Transaktionsprotokoll nicht gesichert. Sie sollten jedes Datenbankwiederherstellungsmodell sorgfältig berücksichtigen: Durchführen regelmäßiger Transaktionsprotokollsicherungen in allen Datenbanken in FULL oder BULK_LOGGED Wiederherstellungsmodellen, um Auftreten von Fehler 9002 zu minimieren. Weitere Informationen finden Sie unter Wiederherstellungsmodelle.

Sichern des Protokolls

Falls bei Verwendung der Wiederherstellungsmodelle FULL oder BULK_LOGGED das Transaktionsprotokoll nicht vor Kurzem gesichert wurde, kann durch die Sicherung eine Protokollkürzung verhindert werden. Sie müssen das Transaktionsprotokoll sichern, damit Protokolldatensätze freigegeben werden können und das Protokoll gekürzt werden kann. Wenn das Protokoll zuvor noch nicht gesichert wurde, müssen Sie zwei Protokollsicherungen erstellen, damit das Protokoll von der Datenbank-Engine bis zu dem Punkt abgeschnitten werden kann, an dem die letzte Sicherung erfolgt ist. Durch Kürzen des Protokolls wird logischer Speicherplatz für neue Protokolldatensätze freigegeben. Sichern Sie das Protokoll regelmäßig und in kürzeren Abständen, damit es nicht wieder so schnell aufgefüllt wird. Weitere Informationen finden Sie unter Wiederherstellungsmodelle.

Ein vollständiger Verlauf aller SQL Server-Sicherungs- und -Wiederherstellungsvorgänge für eine Serverinstanz wird in der msdb-Datenbank gespeichert. Verwenden Sie das folgende Beispielskript, um den vollständigen Sicherungsverlauf einer Datenbank zu überprüfen:

SELECT bs.database_name,
    CASE
        WHEN bs.type = 'D' AND bs.is_copy_only = 0 THEN 'Full Database'
        WHEN bs.type = 'D' AND bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
        WHEN bs.type = 'I' THEN 'Differential database backup'
        WHEN bs.type = 'L' THEN 'Transaction Log'
        WHEN bs.type = 'F' THEN 'File or filegroup'
        WHEN bs.type = 'G' THEN 'Differential file'
        WHEN bs.type = 'P' THEN 'Partial'
        WHEN bs.type = 'Q' THEN 'Differential partial'
    END + ' Backup' AS backuptype,
    bs.recovery_model,
    bs.Backup_Start_Date AS BackupStartDate,
    bs.Backup_Finish_Date AS BackupFinishDate,
    bf.physical_device_name AS LatestBackupLocation,
    bs.backup_size / 1024. / 1024. AS backup_size_mb,
    bs.compressed_backup_size / 1024. / 1024. AS compressed_backup_size_mb,
    database_backup_lsn, -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
    checkpoint_lsn,
    begins_log_chain
FROM msdb.dbo.backupset AS bs
     LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bf
         ON bs.[media_set_id] = bf.[media_set_id]
WHERE recovery_model IN ('FULL', 'BULK-LOGGED')
      AND bs.backup_start_date > DATEADD(month, -2, SYSDATETIME()) --only look at last two months
ORDER BY bs.database_name ASC, bs.Backup_Start_Date DESC;

Ein vollständiger Verlauf aller SQL Server-Sicherungs- und -Wiederherstellungsvorgänge für eine Serverinstanz wird in der msdb-Datenbank gespeichert. Informationen zum Sicherungsverlauf finden Sie unter Sicherungsverlauf und Headerinformationen (SQL Server).

Erstellen einer Transaktionsprotokollsicherung

Beispiel für die Sicherung des Protokolls:

BACKUP LOG [dbname] TO DISK = 'some_volume:\some_folder\dbname_LOG.trn';

Wichtig

Wenn die Datenbank beschädigt ist, finden Sie entsprechende Informationen unter Protokollfragmentsicherungen (SQL Server).

ACTIVE_TRANSACTION log_reuse_wait

Zu den Schritten für die Problembehandlung der Fehlerursache ACTIVE_TRANSACTION gehört das Ermitteln der Transaktion mit langer Ausführungszeit und deren Korrektur (in einigen Fällen mit dem Befehl KILL).

Ermitteln von Transaktionen mit langer Ausführungszeit

Eine Transaktion mit langer Ausführungszeit kann zum Auffüllen des Transaktionsprotokolls führen. Um nach lang laufenden Transaktionen zu suchen, verwenden Sie eine der folgenden Optionen:

  • sys.dm_tran_database_transactions:

    Diese dynamische Verwaltungssicht gibt Informationen zu Transaktionen auf Datenbankebene zurück. Bei einer langfristig ausgeführten Transaktion umfassen Die Spalten von besonderem Interesse die Zeit des ersten Protokolldatensatzes (database_transaction_begin_time), den aktuellen Status der Transaktion (database_transaction_state) und die Protokollsequenznummer (LSN) des BEGIN Datensatzes im Transaktionsprotokoll (database_transaction_begin_lsn).

  • DBCC OPENTRAN:

    Mithilfe dieser Anweisung können Sie die Benutzer-ID des Transaktionsbesitzers identifizieren. Auf diese Weise können Sie die Quelle der Transaktion ermitteln und die Transaktion ordnungsgemäß beenden (durch ein Commit anstelle eines Rollbacks).

Abbrechen einer Transaktion

Gelegentlich müssen Sie die Transaktion lediglich beenden. Dazu müssen Sie möglicherweise die KILL-Anweisung verwenden. Verwenden Sie die KILL Aussage mit äußerster Vorsicht, insbesondere wenn kritische Prozesse ausgeführt werden, die Sie nicht beenden möchten.

CHECKPOINT log_reuse_wait

Seit der letzten Protokollkürzung ist in allen Wiederherstellungsmodellen kein Prüfpunkt aufgetreten, oder der Header des Protokolls wurde noch nicht über eine virtuelle Protokolldatei (Virtual Log File, VLF) hinaus verschoben.

Dies ist ein häufiger Grund für das verzögerte Kürzen von Protokollen. Bei einer Verzögerung sollten Sie die Ausführung des Befehls CHECKPOINT für die Datenbank oder eine Untersuchung der Protokoll-VLFs in Betracht ziehen.

USE dbname;
CHECKPOINT;

SELECT * FROM sys.dm_db_log_info(db_id('dbname'));

AVAILABILITY_REPLICA log_reuse_wait

Wenn Transaktionsänderungen auf dem primären Replikat Always On-Verfügbarkeitsgruppen noch nicht auf dem sekundären Replikat festgeschrieben wurden, kann das Transaktionsprotokoll für das primäre Replikat nicht gekürzt werden. Dies kann zur Vergrößerung des Protokolls führen. Diese Situation kann unabhängig davon auftreten, ob für das sekundäre Replikat der synchrone oder asynchrone Commitmodus festgelegt ist. Informationen zur Behandlung dieser Art von Problemen finden Sie unter Fehler 9002: Das Transaktionsprotokoll für die Datenbank ist aufgrund eines AVAILABILITY_REPLICA-Fehlers voll.

Replikation, Änderungsnachverfolgung oder CDC

Features wie Replikation, Änderungsnachverfolgung und Change Data Capture (CDC) basieren auf dem Transaktionsprotokoll. Wenn Transaktionen oder Änderungen also nicht übermittelt werden, kann es verhindern, dass das Transaktionsprotokoll abgeschnitten wird.

Verwenden Sie DBCC OPENTRAN, Replication Monitor oder gespeicherte Prozeduren für die Änderungsnachverfolgung und CDC, um Probleme mit diesen Features zu untersuchen und zu beheben.

Weitere Informationen zu log_reuse_wait-Faktoren

Weitere Informationen finden Sie in Faktoren, die die Protokollkürzung verzögern können.

2. Auflösen eines vollen Datenträgervolumes

In einigen Situationen kann sich das Datenträgervolume, das die Transaktionsprotokolldatei hostet, auffüllen. Sie können eine der folgenden Aktionen ausführen, um das Szenario „Protokoll voll“ zu beheben, das sich aus einem vollen Datenträger ergibt:

Freier Speicherplatz

Möglicherweise können Sie durch Löschen oder Verschieben anderer Dateien Speicherplatz auf dem Datenträger freigeben, das die Transaktionsprotokolldatei für die Datenbank enthält. Aufgrund des freigegebenen Speicherplatzes kann die Protokolldatei dann durch den Wiederherstellungsmechanismus automatisch vergrößert werden.

Verschieben der Protokolldatei auf einen anderen Datenträger

Wenn Sie auf dem Datenträger, auf dem die Protokolldatei aktuell gespeichert ist, nicht genügend Speicherplatz freigeben können, können Sie die Datei auf einen anderen Datenträger mit ausreichendem Speicherplatz verschieben.

Wichtig

Protokolldateien sollten unter keinen Umständen in komprimierten Dateisystemen gespeichert werden.

Informationen zum Ändern des Speicherorts einer Protokolldatei finden Sie unter Verschieben von Datenbankdateien .

Hinzufügen einer Protokolldatei auf einem anderen Datenträger

Fügen Sie der Datenbank mithilfe von ALTER DATABASE <database_name> ADD LOG FILE eine neue Protokolldatei auf einem anderen Datenträger hinzu, der über genügend Speicherplatz verfügt. Mehrere Protokolldateien für eine einzelne Datenbank sollten nur als vorübergehender Zustand angesehen werden, um ein Speicherplatzproblem zu beheben, nicht als langfristige Lösung. Die meisten Datenbanken sollten nur über eine Transaktionsprotokolldatei verfügen. Fahren Sie mit der Untersuchung des Grunds fort, warum das Transaktionsprotokoll voll ist und nicht gekürzt werden kann. Erwägen Sie das Hinzufügen zusätzlicher temporärer Transaktionsprotokolldateien nur als erweiterten Schritt zur Problembehandlung.

Weitere Informationen finden Sie unter Hinzufügen von Daten oder Protokolldateien mit einer Datenbank.

Diese Schritte können teilweise automatisiert werden, indem Sie das folgende T-SQL-Skript ausführen, um Protokolldateien zu identifizieren, die einen großen Prozentsatz des Speicherplatzes auf dem Datenträger belegen, und Aktionen vorschlägt:

DECLARE @log_reached_disk_size AS BIT = 0;

SELECT [name] AS LogName,
       physical_name,
       CONVERT (BIGINT, size) * 8 / 1024 AS LogFile_Size_MB,
       volume_mount_point,
       available_bytes / 1024 / 1024 AS Available_Disk_space_MB,
       (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 AS file_size_as_percentage_of_disk_space,
       db_name(mf.database_id) AS DbName
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, file_id)
WHERE mf.[type_desc] = 'LOG'
    AND (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 > 90 --log is 90% of disk drive
ORDER BY size DESC;

IF @@ROWCOUNT > 0
    BEGIN
        SET @log_reached_disk_size = 1;
        -- Discover if any logs have filled the volume they reside on, or are close to filling the volume.
        -- Either add a new file to a new drive, or shrink an existing file.
        -- If it cannot shrink, direct the script to recommend next steps.
        DECLARE @db_name_filled_disk AS sysname, @log_name_filled_disk AS sysname, @go_beyond_size AS BIGINT;
        DECLARE log_filled_disk CURSOR
            FOR SELECT db_name(mf.database_id),
                       name
                FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, file_id)
                WHERE mf.[type_desc] = 'LOG'
                      AND (CONVERT (BIGINT, size) * 8.0 / 1024) / (available_bytes / 1024 / 1024) * 100 > 90 --log is 90% of disk drive
                ORDER BY size DESC;
        OPEN log_filled_disk;
        FETCH NEXT FROM log_filled_disk INTO @db_name_filled_disk, @log_name_filled_disk;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SELECT 'Transaction log for database "' + @db_name_filled_disk + '" has nearly or completely filled disk volume it resides on!' AS Finding;
                SELECT 'Consider using one of the below commands to shrink the "' + @log_name_filled_disk + '" transaction log file size or add a new file to a NEW volume' AS Recommendation;
                SELECT 'DBCC SHRINKFILE(''' + @log_name_filled_disk + ''')' AS Shrinkfile_Command;
                SELECT 'ALTER DATABASE ' + @db_name_filled_disk + ' ADD LOG FILE ( NAME = N''' + @log_name_filled_disk + '_new'', FILENAME = N''NEW_VOLUME_AND_FOLDER_LOCATION\' + @log_name_filled_disk + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile;
                SELECT 'If shrink does not reduce the file size, likely it is because it has not been truncated. Please review next section below. See https://learn.microsoft.com/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql' AS TruncateFirst;
                SELECT 'Can you free some disk space on this volume? If so, do this to allow for the log to continue growing when needed.' AS FreeDiskSpace;
                FETCH NEXT FROM log_filled_disk INTO @db_name_filled_disk, @log_name_filled_disk;
            END
        CLOSE log_filled_disk;
        DEALLOCATE log_filled_disk;
    END

3. Ändern des Grenzwerts für die Protokollgröße oder Aktivieren der automatischen Vergrößerung

Fehler 9002 kann generiert werden, wenn die Größe des Transaktionsprotokolls auf eine obere Grenze festgelegt ist oder das Feature für die automatische Vergrößerung nicht zulässig ist. In diesem Fall kann das Problem durch Aktivieren der automatischen Vergrößerung oder durch manuelles Erhöhen der Protokollgröße behoben werden. Verwenden Sie diesen T-SQL-Befehl, um solche Protokolldateien zu suchen und befolgen Sie die bereitgestellten Empfehlungen :

SELECT DB_NAME(database_id) AS DbName,
       name AS LogName,
       physical_name,
       type_desc,
       CONVERT (BIGINT, SIZE) * 8 / 1024 AS LogFile_Size_MB,
       CONVERT (BIGINT, max_size) * 8 / 1024 AS LogFile_MaxSize_MB,
       (SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 AS percent_full_of_max_size,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END AS AutoGrow
FROM sys.master_files
WHERE file_id = 2
      AND (SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 > 90
      AND max_size NOT IN (-1, 268435456)
      OR growth = 0;

IF @@ROWCOUNT > 0
    BEGIN
        DECLARE @db_name_max_size AS sysname, @log_name_max_size AS sysname, @configured_max_log_boundary AS BIGINT, @auto_grow AS INT;
        DECLARE reached_max_size CURSOR
        FOR SELECT db_name(database_id),
                    name,
                    CONVERT (BIGINT, SIZE) * 8 / 1024,
                    growth
            FROM sys.master_files
            WHERE file_id = 2
                AND ((SIZE * 8.0 / 1024) / (max_size * 8.0 / 1024) * 100 > 90
                    AND max_size NOT IN (-1, 268435456)
                    OR growth = 0);
        OPEN reached_max_size;
        FETCH NEXT FROM reached_max_size INTO @db_name_max_size, @log_name_max_size, @configured_max_log_boundary, @auto_grow;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @auto_grow = 0
                BEGIN
                    SELECT 'The database "' + @db_name_max_size + '" contains a log file "' + @log_name_max_size + '" whose autogrow has been DISABLED' AS Finding;
                    SELECT 'Consider enabling autogrow or increasing file size via these ALTER DATABASE commands' AS Recommendation;
                    SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', FILEGROWTH = 65536KB)' AS AutoGrowth;
                END
            ELSE
                BEGIN
                    SELECT 'The database "' + @db_name_max_size + '" contains a log file "' + @log_name_max_size + '" whose max limit is set to ' + CONVERT (VARCHAR (24), @configured_max_log_boundary) + ' MB and this limit has been reached!' AS Finding;
                    SELECT 'Consider using one of the below ALTER DATABASE commands to either change the log file size or add a new file' AS Recommendation;
                END
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = UNLIMITED)' AS UnlimitedSize;
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = something_larger_than_' + CONVERT (VARCHAR (24), @configured_max_log_boundary) + 'MB )' AS IncreasedSize;
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' ADD LOG FILE ( NAME = N''' + @log_name_max_size + '_new'', FILENAME = N''SOME_FOLDER_LOCATION\' + @log_name_max_size + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile;
            FETCH NEXT FROM reached_max_size INTO @db_name_max_size, @log_name_max_size, @configured_max_log_boundary, @auto_grow;
        END
        CLOSE reached_max_size;
        DEALLOCATE reached_max_size;
    END
ELSE
    SELECT 'Found no files that have reached max log file size' AS Findings;

Vergrößern der Protokolldateigröße oder Aktivieren der automatischen Vergrößerung

Wenn auf dem Protokolldatenträger Speicherplatz vorhanden ist, können Sie die Protokolldatei vergrößern. Die maximale Größe für Protokolldateien beträgt zwei Terabyte (TB) pro Protokolldatei.

Wenn die automatische Vergrößerung deaktiviert ist, die Datenbank online ist und auf dem Datenträger ausreichend Speicherplatz verfügbar ist, führen Sie einen der folgenden Schritte aus:

Hinweis

Wenn der aktuelle Größengrenzwert erreicht ist, erhöhen Sie in beiden Fällen den MAXSIZE Wert.

Zugehöriger Inhalt

  • Last updated on