Felsöka en fullständig transaktionslogg (SQL Server-fel 9002)

gäller för:SQL Server

Den här artikeln gäller för SQL Server-instanser.

Alternativ 1: Kör stegen direkt i en körbar notebook-fil via Azure Data Studio

Innan du försöker öppna den här notebook-filen kontrollerar du att Azure Data Studio är installerat på den lokala datorn. Om du vill installera går du till Ladda ned och installera Azure Data Studio.

Alternativ 2: Följ stegen manuellt

Den här artikeln beskriver möjliga svar på en fullständig transaktionslogg och föreslår hur du undviker den i framtiden.

När transaktionsloggen blir full utfärdar SQL Server Database Engine ett 9002-fel. Loggen kan fyllas när databasen är online eller i återställning. Om loggen fylls när databasen är online förblir databasen online, men den kan bara läsas, inte uppdateras. Om loggen fylls under återställningen markerar databasmotorn databasen som RESOURCE PENDING. I båda fallen krävs användaråtgärd för att göra loggutrymmet tillgängligt.

Vanliga orsaker till en fullständig transaktionslogg

Det lämpliga svaret på en fullständig transaktionslogg beror på vilka villkor som gjorde att loggen fylldes. Vanliga orsaker inkluderar:

• Loggen förkortas inte
• Diskvolymen är full
• Loggstorleken är inställd på ett fast maximalt värde eller så inaktiveras autogrow
• Replikering eller tillgänglighetsgruppssynkronisering som inte kan slutföras

Följ dessa specifika steg för att hjälpa dig att hitta orsaken till en fullständig transaktionslogg och lösa problemet.

• Trunkera loggen
• Lösa en fullständig diskvolym
• Ändra loggstorleksgränsen eller aktivera autogrow

1. Trunkera loggen

En vanlig lösning på det här problemet är att se till att säkerhetskopior av transaktionsloggar utförs för databasen, vilket säkerställer att loggen beskärs. Om ingen ny transaktionslogghistorik anges för databasen med en fullständig transaktionslogg är lösningen på problemet enkel: återuppta regelbundna säkerhetskopior av transaktionsloggen för databasen.

Mer information finns i Hantera storleken på transaktionsloggfilen och Krymp en fil.

Förklaring av loggtrunkering

Det finns en skillnad mellan att trunkera en transaktionslogg och krympa en transaktionslogg. Loggtrunkering sker normalt under en säkerhetskopia av transaktionsloggen och är en logisk åtgärd som tar bort committerade poster i loggen, medan loggkrympning frigör fysiskt utrymme i filsystemet genom att minska filstorleken. Loggtrunkering sker vid en VLF-gräns (virtual-log-file), och en loggfil kan innehålla många VLF. En loggfil kan bara krympas om det finns tomt utrymme i loggfilen att frigöra. Att krympa en loggfil ensam kan inte lösa problemet med en fullständig loggfil. I stället måste du ta reda på varför loggfilen är full och inte kan göras mindre.

Vad förhindrar loggtrunkering?

Om du vill ta reda på vad som förhindrar loggtrunkering i ett visst fall använder du kolumnerna log_reuse_wait och log_reuse_wait_desc i sys.databases katalogvyn. Mer information finns i sys.databases. Beskrivningar av faktorer som kan fördröja loggtrunkering finns i Transaktionsloggen.

Följande T-SQL-kommandon hjälper dig att upptäcka om en databastransaktionslogg inte har trunkerats och vad orsaken till detta kan vara. Följande skript rekommenderar också steg för att lösa problemet:

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;

LOGG_SÄKERHETSKOPIA loggåteranvänd_fördröjning

Den vanligaste åtgärden att tänka på om du ser LOG_BACKUP eller log_reuse_wait är att granska databasens återställningsmodell och säkerhetskopiera transaktionsloggen för databasen.

Överväg databasens återställningsmodell

Transaktionsloggen kan ha problem att trunkera med kategorin LOG_BACKUP eller log_reuse_wait, eftersom du aldrig har säkerhetskopierat den. I många av dessa fall använder din databas FULL- eller BULK_LOGGED-återställningsmodellen, men du säkerhetskopierade inte transaktionsloggen. Du bör överväga varje databasåterställningsmodell noggrant: utför regelbundna säkerhetskopieringar av transaktionsloggar på alla databaser i FULL eller BULK_LOGGED återställningsmodeller för att minimera förekomster av fel 9002. Mer information finns i Recovery-modeller.

Säkerhetskopiera loggen

Under återställningsmodellen FULL eller BULK_LOGGED, om transaktionsloggen inte har säkerhetskopierats nyligen, kan det vara säkerhetskopieringen som förhindrar loggtrunkering. Du måste utföra en säkerhetskopiering av transaktionsloggen så att loggposter kan frigöras och loggen förkortas. Om loggen aldrig har säkerhetskopierats måste du skapa två loggsäkerhetskopior så att databasmotorn kan trunkera loggen till den tidpunkt då den senaste säkerhetskopieringen inträffade. Om du trunkerar loggen frigörs logiskt utrymme för nya loggposter. Om du vill förhindra att loggen fylls i igen kan du göra loggsäkerhetskopior regelbundet och oftare. Mer information finns i Recovery-modeller.

En fullständig historik över alla säkerhetskopierings- och återställningsåtgärder för SQL Server på en serverinstans lagras i systemdatabasen msdb . Om du vill granska den fullständiga säkerhetskopieringshistoriken för en databas använder du följande exempelskript:

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;

En fullständig historik över alla säkerhetskopierings- och återställningsåtgärder för SQL Server på en serverinstans lagras i systemdatabasen msdb . Mer information om säkerhetskopieringshistorik finns i Säkerhetskopieringshistorik och rubrikinformation (SQL Server).

Skapa en säkerhetskopiering av transaktionslogg

Exempel på hur du säkerhetskopierar loggen:

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

• Säkerhetskopiera en transaktionslogg

• SqlBackup (SMO)

Aktiv transaktion log_reuse_wait

Stegen för att felsöka ACTIVE_TRANSACTION orsaken är att identifiera den tidskrävande transaktionen och lösa den (i vissa fall med hjälp av KILL kommandot för att göra det).

Identifiera långvariga transaktioner

En långvarig transaktion kan göra att transaktionsloggen fylls i. Om du vill söka efter långvariga transaktioner använder du något av följande alternativ:

• sys.dm_tran_database_transactions:

  Den här dynamiska hanteringsvyn returnerar information om transaktioner på databasnivå. För en långvarig transaktion inkluderar kolumner av särskilt intresse tiden för den första loggposten (database_transaction_begin_time), transaktionens aktuella tillstånd (database_transaction_state) och loggsekvensnumret (LSN) för BEGIN posten i transaktionsloggen (database_transaction_begin_lsn).

• DBCC OPENTRAN:

  Med detta uttalande kan du identifiera användar-ID:t för ägaren av transaktionen, så att du möjligen kan spåra upp källan till transaktionen för en mer ordnad avslutning (slutföra den istället för att återställa den).

Avsluta en transaktion

Ibland behöver du bara avsluta transaktionen. du kanske måste använda KILL-instruktionen . Använd -instruktionen KILL med extrem försiktighet, särskilt när kritiska processer körs som du inte vill avsluta.

KONTROLLPUNKT log_reuse_wait

Ingen kontrollpunkt har inträffat sedan den senaste loggtrunkeringen, eller så har logghuvudet ännu inte flyttats bortom en virtuell loggfil (VLF) i alla återställningsmodeller.

Det här är en rutinmässig orsak till att fördröja loggtrunkering. Om det är fördröjt kan du överväga att CHECKPOINT köra kommandot på databasen eller undersöka logg-VLFs.

USE dbname;
CHECKPOINT;

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

Tillgänglighetsreplika logg_återanvändningsväntan

När transaktionsändringar på den primära AlwaysOn-tillgänglighetsgruppens replik ännu inte är härdade på den sekundära repliken kan den primära repliktransaktionsloggen inte trunkeras. Detta kan göra att loggen växer och inträffar om den sekundära repliken är inställd för synkront eller asynkront incheckningsläge. Information om hur du felsöker den här typen av problem finns i Fel 9002. Transaktionsloggen för databasen är full på grund av AVAILABILITY_REPLICA fel.

Replikering, ändringsspårning eller CDC

Funktioner som replikering, ändringsspårning och ändringsdatainsamling (CDC) förlitar sig på transaktionsloggen, så om transaktioner eller ändringar inte levereras kan det förhindra att transaktionsloggen trunkerar.

Använd DBCC OPENTRAN, Replication Monitor eller lagrade procedurer för ändringsspårning och CDC för att undersöka och lösa eventuella problem med dessa funktioner.

Hitta information om log_reuse_wait-faktorer

Mer information finns i Faktorer som kan fördröja loggtrunkering.

2. Lös hela diskvolymen

I vissa fall kan diskvolymen som är värd för transaktionsloggfilen fyllas i. Du kan utföra någon av följande åtgärder för att lösa det fullständiga loggscenariot som är resultatet av en fullständig disk:

Ledigt diskutrymme

Du kanske kan frigöra diskutrymme på diskenheten som innehåller transaktionsloggfilen för databasen genom att ta bort eller flytta andra filer. Det frigjorda diskutrymmet gör att återställningssystemet kan förstora loggfilen automatiskt.

Flytta loggfilen till en annan disk

Om du inte kan frigöra tillräckligt med diskutrymme på den enhet som för närvarande innehåller loggfilen kan du överväga att flytta filen till en annan enhet med tillräckligt med utrymme.

Mer information om hur du ändrar platsen för en loggfil finns i Flytta databasfiler .

Lägga till en loggfil på en annan disk

Lägg till en ny loggfil i databasen på en annan disk som har tillräckligt med utrymme med hjälp ALTER DATABASE <database_name> ADD LOG FILEav . Flera loggfiler för en enskild databas bör betraktas som ett tillfälligt villkor för att lösa ett utrymmesproblem, inte ett långsiktigt villkor. De flesta databaser bör bara ha en transaktionsloggfil. Fortsätt att undersöka orsaken till att transaktionsloggen är full och inte kan trunkeras. Överväg att lägga till ytterligare tillfälliga transaktionsloggfiler som ett avancerat felsökningssteg.

Mer information finns i Lägg till data eller loggfiler i en databas.

Verktygsskript för rekommenderade åtgärder

De här stegen kan delvis automatiseras genom att köra följande T-SQL-skript för att identifiera loggfiler som använder en stor andel diskutrymme och föreslå åtgärder:

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. Ändra loggstorleksgräns eller aktivera automatisk tillväxt

Fel 9002 kan genereras om transaktionsloggens storlek är inställd på en övre gräns eller om funktionen autogrow inte tillåts. I det här fallet kan du lösa problemet genom att aktivera automatisk tillväxt eller öka loggstorleken manuellt. Använd det här T-SQL-kommandot för att hitta sådana loggfiler och följ rekommendationerna:

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;

Öka loggfilens storlek eller aktivera automatisk tillväxt

Om det finns utrymme på loggdisken kan du öka loggfilens storlek. Den maximala storleken för loggfiler är 2 terabyte (TB) per loggfil.

Om autogrow är inaktiverat är databasen online och det finns tillräckligt med utrymme på disken, överväg att utföra följande steg:

• Öka filstorleken manuellt för att skapa en enskild storleksökning. Det här är allmänna rekommendationer om loggstorlekstillväxt och storlek.

• Aktivera automatisk tillväxt med hjälp av -instruktionen ALTER DATABASE för att ange en ökning av icke-zero-tillväxt för FILEGROWTH alternativet. Se Överväganden för inställningar av automatisk storleksökning och automatisk krympning i SQL Server.

Relaterat innehåll

• ÄNDRA DATABAS (Transact-SQL)
• Hantera storleken på transaktionsloggfilen
• säkerhetskopieringar av transaktionsloggar (SQL Server)
• sp_add_log_file_recover_suspect_db (Transact-SQL)
• MSSQLSERVER_9002
• Hur en loggfilstruktur kan påverka databasåterställningstiden – Microsoft Tech Community