Problemen met een volledig transactielogboek oplossen (SQL Server-fout 9002)

van toepassing op:SQL Server-

Dit artikel is van toepassing op SQL Server-exemplaren.

Optie 1: Voer de stappen rechtstreeks uit in een uitvoerbaar notebook via Azure Data Studio

Voordat u dit notebook probeert te openen, controleert u of Azure Data Studio is geïnstalleerd op uw lokale computer. Als u wilt installeren, gaat u naar Azure Data Studio downloaden en installeren.

Optie 2: Volg de stappen handmatig

In dit artikel worden mogelijke reacties op een volledig transactielogboek besproken en wordt voorgesteld hoe u dit in de toekomst kunt voorkomen.

Wanneer het transactielogboek vol raakt, geeft de SQL Server Database Engine een 9002-fout uit. Het logboek kan worden ingevuld wanneer de database online is of in herstel is. Als het logboek vol raakt terwijl de database online is, blijft de database online maar kan deze alleen worden gelezen, niet worden bijgewerkt. Als het logboek tijdens het herstel wordt ingevuld, markeert de database-engine de database als RESOURCE PENDING. In beide gevallen is gebruikersactie vereist om logboekruimte beschikbaar te maken.

Veelvoorkomende redenen voor een volledig transactielogboek

Het juiste antwoord op een volledig transactielogboek is afhankelijk van de omstandigheden die ervoor hebben gezorgd dat het logboek vol raakt. Veelvoorkomende oorzaken zijn onder andere:

• Logbestand wordt niet afgekort
• Het schijfvolume is vol.
• De logboekgrootte is ingesteld op een vaste maximumwaarde of automatische groei is uitgeschakeld
• Synchronisatie van replicatie of beschikbaarheidsgroep die niet kan worden voltooid

Volg deze specifieke stappen om de reden voor een volledig transactielogboek te vinden en het probleem op te lossen.

• Het logbestand inkorten
• Een volledig schijfvolume oplossen
• De limiet voor de logboekgrootte wijzigen of automatische groei inschakelen

1. Het logboek afkappen

Een veelvoorkomende oplossing voor dit probleem is ervoor te zorgen dat back-ups van transactielogboeken worden uitgevoerd voor uw database, waardoor het logboek wordt afgekapt. Als er geen recente transactielogboekgeschiedenis wordt aangegeven voor de database met een volledig transactielogboek, is de oplossing voor het probleem eenvoudig: regelmatige back-ups van transactielogboeken van de database hervatten.

Zie De grootte van het transactielogboekbestand beheren en een bestand verkleinen voor meer informatie.

Uitleg over het inkorten van logbestanden

Er is een verschil tussen het trunceren van een transactielogboek en het verkleinen van een transactielogboek. Logboekafkapping vindt normaal plaats tijdens een back-up van een transactielogboek en is een logische bewerking waarmee vastgelegde records in het logboek worden verwijderd, terwijl het verkleinen van logboeken fysieke ruimte vrij maakt op het bestandssysteem door de bestandsgrootte te verkleinen. Logboektruncatie vindt plaats op een virtual-log-file (VLF)-grens, en een logbestand kan veel VLF's bevatten. Een logboekbestand kan alleen worden verkleind als er lege ruimte in het logboekbestand is om vrij te maken. Als u alleen een logboekbestand verkleint, kan het probleem van een volledig logboekbestand niet worden opgelost. In plaats daarvan moet u ontdekken waarom het logboekbestand vol is en niet kan worden afgekapt.

Wat voorkomt afkapping van logboeken?

Als u wilt ontdekken wat afkapping van logboeken in een bepaald geval verhindert, gebruikt u de log_reuse_wait en log_reuse_wait_desc kolommen van de sys.databases catalogusweergave. Zie sys.databases voor meer informatie. Zie Het transactielogboek voor beschrijvingen van factoren die de afkapping van logboeken kunnen vertragen.

Met de volgende set T-SQL-opdrachten kunt u vaststellen of een databasetransactielogboek niet is afgekapt en de reden hiervoor. Het volgende script raadt ook stappen aan om het probleem op te lossen:

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://free.blessedness.top/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://free.blessedness.top/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://free.blessedness.top/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;

LOG_BACKUP log_reuse_wait

De meest voorkomende acties die u moet overwegen als u LOG_BACKUP of log_reuse_wait ziet, zijn uw databaseherstelmodel te beoordelen en een back-up te maken van het transactielogboek van uw database.

Overweeg het herstelmodel van de database

Het transactielogboek kan mogelijk niet worden afgekapt met LOG_BACKUP of log_reuse_wait categorie, omdat u er nooit een back-up van hebt gemaakt. In veel van deze gevallen maakt uw database gebruik van het FULL of BULK_LOGGED herstelmodel, maar u hebt geen back-up gemaakt van uw transactielogboek. U moet elk databaseherstelmodel zorgvuldig overwegen: regelmatige back-ups van transactielogboeken uitvoeren op alle databases in FULL of BULK_LOGGED herstelmodellen, om het voorkomen van fout 9002 te minimaliseren. Zie Recovery-modellenvoor meer informatie.

Een back-up van het logboek maken

Onder het FULL of BULK_LOGGED herstelmodel, als het transactielogboek onlangs niet is geback-upt, kan de back-up zijn wat voorkomt dat het logboek wordt afgekapt. U moet een back-up maken van het transactielogboek om logboekrecords vrij te geven en het logboek in te korten. Als er nog nooit een back-up van het logboek is gemaakt, moet u twee logboekback-ups maken , zodat de database-engine het logboek kan afkappen tot het punt van de laatste back-up. Als u het logboek afkapt, wordt logische ruimte vrijgemaakt voor nieuwe logboekrecords. Als u wilt voorkomen dat het logboek weer volloopt, moet u regelmatig en vaker logboekback-ups maken. Zie Recovery-modellenvoor meer informatie.

Een volledige geschiedenis van alle back-up- en herstelbewerkingen van SQL Server op een serverexemplaren wordt opgeslagen in de msdb systeemdatabase. Als u de volledige back-upgeschiedenis van een database wilt bekijken, gebruikt u het volgende voorbeeldscript:

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;

Een volledige geschiedenis van alle back-up- en herstelbewerkingen van SQL Server op een serverexemplaren wordt opgeslagen in de msdb systeemdatabase. Zie Back-upgeschiedenis en headerinformatie (SQL Server) voor meer informatie over back-upgeschiedenis.

Een back-up van een transactielogboek maken

Voorbeeld van het maken van een back-up van het logboek:

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

• een back-up maken van een transactielogboek

• SqlBackup (SMO)

ACTIVE_TRANSACTION log_reuse_wait

De stappen voor het oplossen ACTIVE_TRANSACTION van de reden zijn het detecteren van de langlopende transactie en het oplossen ervan (in sommige gevallen met behulp van de KILL opdracht hiervoor).

Langlopende transacties detecteren

Een langlopende transactie kan ertoe leiden dat het transactielogboek wordt gevuld. Gebruik een van de volgende opties om te zoeken naar langlopende transacties:

• sys.dm_tran_database_transactions:

  Deze dynamische beheerweergave retourneert informatie over transacties op databaseniveau. Voor een langlopende transactie bevatten kolommen met een bepaald belang de tijd van de eerste logboekrecord (database_transaction_begin_time), de huidige status van de transactie (database_transaction_state) en het logboekreeksnummer (LSN) van de BEGIN record in het transactielogboek (database_transaction_begin_lsn).

• DBCC OPENTRAN:

  Met deze instructie kunt u de gebruikers-id van de eigenaar van de transactie identificeren, zodat u mogelijk de bron van de transactie kunt opsporen voor een meer ordelijke beëindiging (doorvoeren in plaats van deze terug te draaien).

Een transactie beëindigen

Soms moet u de transactie beëindigen; Mogelijk moet u de KILL-instructie gebruiken. Gebruik de KILL instructie met extreme voorzichtigheid, met name wanneer kritieke processen worden uitgevoerd die u niet wilt beëindigen.

CONTROLEPOST log_reuse_wait

Er is geen controlepunt opgetreden sinds de laatste afkapping van het logboek, of het hoofd van het logboek is nog niet verder verplaatst dan een virtueel logbestand (VLF), in alle herstelmodellen.

Dit is een routinematige reden voor het vertragen van logboektruncatie. Als dit vertraagd is, kunt u overwegen de CHECKPOINT opdracht uit te voeren in de database of de LOGBOEK-VLF's te bekijken.

USE dbname;
CHECKPOINT;

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

AVAILABILITY_REPLICA log_opnieuw_gebruik_wachten

Wanneer transactiewijzigingen op de primaire AlwaysOn-beschikbaarheidsgroepreplica nog niet zijn beperkt op de secundaire replica, kan het transactielogboek van de primaire replica niet worden afgekapt. Dit kan ertoe leiden dat het logboek groeit, ongeacht of de secundaire replica is ingesteld op synchrone of asynchrone doorvoermodus. Zie Fout 9002 voor informatie over het oplossen van dit type probleem . Het transactielogboek voor de database is vol vanwege AVAILABILITY_REPLICA fout.

Replicatie, wijzigingen bijhouden of CDC

Functies zoals replicatie, wijzigingen bijhouden en het vastleggen van wijzigingengegevens (CDC) zijn afhankelijk van het transactielogboek, dus als transacties of wijzigingen niet worden geleverd, kan het transactielogboek voorkomen dat het transactielogboek wordt afgekapt.

Gebruik DBCC OPENTRAN, Replication Monitor of opgeslagen procedures voor het bijhouden van wijzigingen en CDC om eventuele problemen met deze functies te onderzoeken en op te lossen.

Zoek informatie over log_reuse_wait-factoren

Zie Factoren die het afkappen van logboeken kunnen vertragenvoor meer informatie.

2. Volume van volledige schijf oplossen

In sommige gevallen kan het schijfvolume dat als host fungeert voor het transactielogboekbestand vol raken. U kunt een van de volgende acties uitvoeren om het scenario voor het volledige logboek op te lossen dat het resultaat is van een volledige schijf:

Vrije schijfruimte

Mogelijk kunt u schijfruimte vrijmaken op het schijfstation dat het transactielogboekbestand voor de database bevat door andere bestanden te verwijderen of te verplaatsen. Met de vrije schijfruimte kan het herstelsysteem het logboekbestand automatisch vergroten.

Het logboekbestand naar een andere schijf verplaatsen

Als u onvoldoende schijfruimte kunt vrij maken op het station dat momenteel het logboekbestand bevat, kunt u overwegen het bestand met voldoende ruimte naar een ander station te verplaatsen.

Zie Databasebestanden verplaatsen voor informatie over het wijzigen van de locatie van een logboekbestand.

Een logboekbestand op een andere schijf toevoegen

Voeg een nieuw logboekbestand toe aan de database op een andere schijf met voldoende ruimte met behulp van ALTER DATABASE <database_name> ADD LOG FILE. Meerdere logboekbestanden voor één database moeten worden beschouwd als een tijdelijke voorwaarde om een ruimteprobleem op te lossen, niet als een voorwaarde op lange termijn. De meeste databases mogen slechts één transactielogboekbestand hebben. Ga door met het onderzoeken van de reden waarom het transactielogboek vol is en niet kan worden afgekapt. Overweeg om extra tijdelijke transactielogboekbestanden toe te voegen als een geavanceerde probleemoplossingsstap.

Zie Gegevens of logboekbestanden toevoegen aan een databasevoor meer informatie.

Hulpprogrammascript voor aanbevolen acties

Deze stappen kunnen gedeeltelijk worden geautomatiseerd door het volgende T-SQL-script uit te voeren om logboekbestanden te identificeren die een groot percentage schijfruimte gebruiken en acties voorstellen:

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://free.blessedness.top/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. De limiet voor logboekgrootte wijzigen of automatisch groeien inschakelen

Fout 9002 kan worden gegenereerd als de grootte van het transactielogboek is ingesteld op een bovengrens of als de functie voor automatisch groeien niet is toegestaan. In dit geval kunt u het probleem oplossen door automatisch groei in te schakelen of de logboekgrootte handmatig te vergroten. Gebruik deze T-SQL-opdracht om dergelijke logboekbestanden te vinden en volg de opgegeven aanbevelingen:

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;

De grootte van logboekbestanden vergroten of automatisch groeien inschakelen

Als er ruimte beschikbaar is op de logboekschijf, kunt u de grootte van het logboekbestand vergroten. De maximale grootte voor logboekbestanden is 2 terabytes (TB) per logboekbestand.

Als automatisch groeien is uitgeschakeld, is de database online en is er voldoende ruimte beschikbaar op de schijf, overweeg dan de volgende stappen uit te voeren:

• Verhoog de bestandsgrootte handmatig om één groeiverhoging te produceren. Dit zijn algemene aanbevelingen voor de groei en grootte van logboeken.

• Schakel automatisch groeien in door de ALTER DATABASE verklaring te gebruiken om een niet-nul groeitoename in te stellen voor de FILEGROWTH optie. Zie Overwegingen voor de instellingen voor automatische groei en automatische verkleining in SQL Server.

Verwante inhoud

• ALTER DATABANK (Transact-SQL)
• De grootte van het transactielogboekbestand beheren
• back-ups van transactielogboeken (SQL Server)
• sp_add_log_file_recover_suspect_db (Transact-SQL)
• MSSQLSERVER_9002
• Hoe een structuur van logboekbestanden van invloed kan zijn op de hersteltijd van de database - Microsoft Tech Community