Sdílet prostřednictvím

Řešení potíží s úplným transakčním protokolem (chyba SQL Serveru 9002)

platí pro:SQL Server

Tento článek se týká instancí SQL Serveru.

Poznámka:

Tento článek se zaměřuje na SQL Server. Konkrétnější informace o této chybě na platformách Azure SQL najdete v tématu Řešení chyb transakčního protokolu pomocí služby Azure SQL Database a řešení chyb transakčního protokolu ve službě Azure SQL Managed Instance. Azure SQL Database a Azure SQL Managed Instance jsou založené na nejnovější stabilní verzi databázového stroje Microsoft SQL Serveru, takže většina obsahu je podobná, i když se možnosti řešení potíží a nástroje můžou lišit.

Možnost 1: Spuštění kroků přímo ve spustitelném poznámkovém bloku přes Azure Data Studio

Než se pokusíte tento poznámkový blok otevřít, zkontrolujte, jestli je na místním počítači nainstalovaná sada Azure Data Studio. Instalaci provedete tak, že přejdete na Stažení a instalaci nástroje Azure Data Studio.

Otevřít poznámkový blok v nástroji Azure Data Studio

Možnost 2: Postupujte podle kroků ručně.

Tento článek popisuje možné odpovědi na úplný transakční protokol a navrhuje, jak se tomu vyhnout v budoucnu.

Když se transakční protokol zaplní, databázový stroj SQL Serveru vydá chybu 9002. Protokol se může zaplnit, když je databáze online nebo v režimu obnovení. Pokud se protokol zaplní, když je databáze online, databáze zůstane online, ale bude umožňovat pouze čtení, nikoli aktualizaci. Pokud se protokol vyplní během obnovení, databázový stroj označí databázi jako RESOURCE PENDING. V obou případech je vyžadována akce uživatele, aby bylo uvolněno místo v protokolu.

Běžné důvody úplného transakčního protokolu

Odpovídající odpověď na úplný transakční protokol závisí na tom, jaké podmínky způsobily vyplnění protokolu. Mezi obvyklé příčiny patří:

  • Protokol se nezkracuje
  • Diskový svazek je plný
  • Velikost protokolu je nastavená na pevnou maximální hodnotu nebo je zakázáno automatické zvětšování.
  • Replikace nebo synchronizace skupin dostupnosti, která se nedá dokončit

Tento konkrétní postup vám pomůže najít důvod úplného transakčního protokolu a vyřešit problém.

1. Zkrácení protokolu

Běžným řešením tohoto problému je zajistit, aby se pro vaši databázi prováděly zálohy transakčních protokolů, což zajišťuje zkrácení protokolu. Pokud není u databáze s plným transakčním protokolem uvedena žádná nedávná historie, řešení problému je jednoduché: obnovit pravidelné zálohování transakčních protokolů databáze.

Další informace najdete v tématu Správa velikosti souboru transakčního protokolu a zmenšení souboru.

Vysvětlení zkrácení protokolu

Mezi zkrácením transakčního protokolu a zmenšením transakčního protokolu je rozdíl. Zkrácení protokolu se obvykle provádí během zálohování transakčního protokolu a je logická operace, která odebere potvrzené záznamy v protokolu, zatímco zmenšení protokolu uvolní fyzické místo v systému souborů zmenšením velikosti souboru. Zkrácení protokolu probíhá na hranici virtuálního protokolu (VLF) a soubor protokolu může obsahovat mnoho souborů VLF. Soubor protokolu se může zmenšit jenom v případě, že je uvnitř souboru protokolu prázdné místo, které se má uvolnit. Zmenšení samotného souboru protokolu nemůže vyřešit problém s úplným souborem protokolu. Místo toho musíte zjistit, proč je soubor protokolu plný a nejde ho zkrátit.

Výstraha

Data, která jsou přesunuta za účelem zmenšení souboru, mohou být rozptýlena na jakékoliv dostupné místo v souboru. To způsobuje fragmentaci indexu a může zpomalit výkon dotazů, které prohledávají rozsah indexu. Pokud chcete fragmentaci odstranit, zvažte opětovné sestavení indexů v souboru po zmenšení. Další informace naleznete v tématu Zmenšení databáze.

Co brání zkrácení protokolu?

Pokud chcete zjistit, co brání zkrácení protokolu v daném případě, použijte sloupce log_reuse_wait a log_reuse_wait_desc zobrazení katalogu sys.databases. Další informace naleznete v tématu sys.databases. Popisy faktorů, které mohou zpozdit zkrácení protokolu, naleznete v transakčním protokolu.

Následující sada příkazů T-SQL vám pomůže identifikovat, jestli není protokol transakcí databáze zkrácený a proč. Následující skript také doporučuje kroky k vyřešení problému:

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 AS Used_log_size_MB,
    lsu.total_log_size_in_bytes / 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;

Důležité

Pokud došlo k chybě 9002 během obnovování databáze a po vyřešení problému, obnovte databázi pomocí ALTER DATABASE database_name SET ONLINE.

LOG_BACKUP (záloha protokolu) log_reuse_wait (čekání na opětovné použití protokolu)

Nejběžnější akcí, kterou je potřeba zvážit, pokud se zobrazí LOG_BACKUP nebo log_reuse_wait je zkontrolovat model obnovení databáze, a zálohovat transakční protokol databáze.

Zvažte model obnovení databáze.

Transakční protokol se může s kategorií LOG_BACKUP nebo log_reuse_wait nepodařit zkracovat, protože jste ho nikdy nezazálohovali. V mnoha těchto případech vaše databáze používá model obnovení FULL nebo BULK_LOGGED, ale transakční protokol jste nezálohovali. Každý model obnovení databáze byste měli pečlivě zvážit: pravidelně zálohujte transakční protokoly pro všechny databáze v FULL modelech nebo BULK_LOGGED modely obnovení, abyste minimalizovali výskyty chyby 9002. Další informace naleznete v tématu modely obnovení.

Zálohujte protokol

Podle modelu obnovení FULL nebo BULK_LOGGED, pokud se protokol transakcí v nedávné době nezazálohoval, to může být příčinou, proč nelze protokol zkrátit. Abyste umožnili uvolnění záznamů protokolu a zkrácení protokolu, musíte zálohovat transakční protokol. Pokud se protokol nikdy nezazálohoval, musíte vytvořit dvě zálohy protokolů , aby databázový stroj umožnil zkrátit protokol na bod poslední zálohy. Zkrácení protokolu uvolní logické místo pro nové záznamy protokolu. Pravidelnými a častějšími zálohami protokolů zabráníte jejich znovu zaplnění. Další informace naleznete v tématu modely obnovení.

Úplná historie všech operací zálohování a obnovení SQL Serveru v instanci serveru je uložena msdb v systémové databázi. Pokud chcete zkontrolovat úplnou historii zálohování databáze, použijte následující ukázkový skript:

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;

Úplná historie všech operací zálohování a obnovení SQL Serveru v instanci serveru je uložena msdb v systémové databázi. Další informace o historii zálohování najdete v tématu Historie zálohování a informace hlavičky (SQL Server).

Vytvoření zálohy transakčního protokolu

Příklad zálohování protokolu:

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

Důležité

Pokud je databáze poškozená, podívejte se na zálohy koncového protokolu (SQL Server).

AKTIVNÍ_TRANSAKCE log_opětovné_použití_čekání

Mezi kroky při řešení příčiny kódu ACTIVE_TRANSACTION patří zjištění dlouhotrvající transakce a její řešení (v některých případech k tomu použít příkaz KILL).

Zjišťování dlouhotrvajících transakcí

Dlouhotrvající transakce může způsobit zaplnění transakčního logu. Pokud chcete vyhledat dlouhotrvající transakce, použijte jednu z následujících možností:

  • sys.dm_tran_database_transactions:

    Toto zobrazení dynamické správy vrací informace o transakcích na úrovni databáze. U dlouhotrvající transakce zahrnují sloupce s určitým zájmem čas prvního záznamu protokolu (database_transaction_begin_time), aktuální stav transakce (database_transaction_state) a pořadové číslo protokolu (LSN)BEGIN záznamu v transakčním protokolu (database_transaction_begin_lsn).

  • DBCC OPENTRAN:

    Toto prohlášení vám umožňuje identifikovat ID uživatele vlastníka transakce, takže můžete potenciálně sledovat zdroj transakce a usnadnit ukončení (potvrzením místo jej vracet zpět).

Ukončení transakce

Někdy stačí ukončit transakci; Možná budete muset použít příkaz KILL . Použijte příkaz KILL s extrémní opatrností, zejména pokud jsou spuštěny kritické procesy, které nechcete ukončit.

kontrolní bod log_reuse_wait

Od posledního zkrácení protokolu nedošlo k žádnému kontrolnímu bodu nebo se hlavička protokolu ještě nepřesunula za soubor virtuálního protokolu (VLF) ve všech modelech obnovení.

Toto je běžný důvod pro zpoždění trunkování protokolu. Pokud je zpoždění, zvažte spuštění CHECKPOINT příkazu v databázi nebo prozkoumání protokolů VLF.

USE dbname;
CHECKPOINT;

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

Pomocný záznam "log_reuse_wait" repliky dostupnosti

Pokud změny transakcí v primární replice skupiny dostupnosti AlwaysOn ještě nejsou na sekundární replice posíleny, nelze protokol transakcí primární repliky zkrátit. To může způsobit růst protokolu, a to bez ohledu na to, zda je sekundární replika nastavena pro synchronní nebo asynchronní režim potvrzení. Informace o řešení tohoto typu problému naleznete v tématu Chyba 9002. Transakční protokol databáze je plný kvůli chybě AVAILABILITY_REPLICA.

Replikace, sledování změn nebo CDC

Funkce, jako je replikace, sledování změn a zachytávání dat změn (CDC), spoléhají na transakční protokol, takže pokud se transakce nebo změny nedoručí, může zabránit zkrácení transakčního protokolu.

Ke zkoumání a řešení problémů s těmito funkcemi použijte DBCC OPENTRAN, Replication Monitor nebo uložené procedury pro sledování změn a CDC .

Vyhledat informace o faktorech log_reuse_wait

Další informace naleznete v tématu Faktory, které mohou zpozdit zkrácení protokolu.

2. Řešení úplného svazku disku

V některých situacích se může zaplnit diskový svazek, který obsahuje soubor transakčního protokolu. Pokud chcete vyřešit scénář zaplněného protokolu, který je výsledkem plného disku, můžete provést jednu z následujících akcí:

Volné místo na disku

Místo na disku, které obsahuje soubor transakčního protokolu pro databázi, můžete uvolnit odstraněním nebo přesunutím jiných souborů. Volné místo na disku umožňuje systému obnovení automaticky zvětšit soubor protokolu.

Přesunutí souboru protokolu na jiný disk

Pokud nemůžete uvolnit dostatek místa na disku, který aktuálně obsahuje soubor protokolu, zvažte přesunutí souboru na jinou jednotku s dostatečným místem.

Důležité

Soubory protokolu by nikdy neměly být umístěny do komprimovaných systémů souborů.

Informace o tom, jak změnit umístění souboru protokolu, najdete v tématu Přesunutí souborů databáze .

Přidání souboru protokolu na jiný disk

Přidejte do databáze nový soubor protokolu na jiném disku, který má dostatek místa pomocí ALTER DATABASE <database_name> ADD LOG FILE. Několik souborů protokolu pro jednu databázi by mělo být považováno za dočasnou podmínku pro vyřešení problému s místem, ne za dlouhodobou podmínku. Většina databází by měla mít pouze jeden soubor transakčního protokolu. Pokračujte a prozkoumejte důvod, proč je transakční protokol plný a nejde ho zkrátit. Zvažte přidání dalších dočasných souborů protokolu transakcí pouze jako pokročilý krok řešení potíží.

Další informace naleznete v tématu Přidání dat nebo souborů protokolu do databáze.

Tyto kroky je možné částečně automatizovat spuštěním následujícího skriptu T-SQL, který identifikuje soubory protokolů, které používají velké procento místa na disku a navrhují akce:

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. Změna limitu velikosti protokolu nebo povolení automatického zvětšování

Chyba 9002 se dá vygenerovat, pokud je velikost transakčního protokolu nastavená na horní limit nebo funkce automatického zvětšování není povolená. V takovém případě lze problém vyřešit povolením funkce automatického rozšíření nebo ručním zvětšením velikosti protokolu. Pomocí tohoto příkazu T-SQL vyhledejte takové soubory protokolů a postupujte podle uvedených doporučení:

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;

Zvětšení velikosti souboru protokolu nebo povolení automatického zvětšování

Pokud je na disku protokolu k dispozici místo, můžete zvětšit velikost souboru protokolu. Maximální velikost souborů protokolu je 2 terabajty (TB) na soubor protokolu.

Pokud je funkce autogrow zakázaná, databáze je online a dostatek místa na disku, zvažte následující kroky:

Poznámka:

V obou případech, pokud je dosaženo aktuálního limitu velikosti, zvyšte MAXSIZE hodnotu.

Související obsah