Udostępnij za pośrednictwem

Rozwiązywanie problemów z pełnym dziennikiem transakcji (błąd programu SQL Server 9002)

Dotyczy:programu SQL Server

Ten artykuł dotyczy wystąpień programu SQL Server.

Uwaga / Notatka

Ten artykuł koncentruje się na programie SQL Server. Aby uzyskać bardziej szczegółowe informacje na temat tego błędu na platformach Azure SQL, zobacz Rozwiązywanie problemów z błędami dziennika transakcji w usłudze Azure SQL Database i Rozwiązywanie problemów z błędami dziennika transakcji w usłudze Azure SQL Managed Instance. Usługi Azure SQL Database i Azure SQL Managed Instance są oparte na najnowszej stabilnej wersji aparatu bazy danych programu Microsoft SQL Server, więc większość zawartości jest podobna, chociaż opcje rozwiązywania problemów i narzędzia mogą się różnić.

Opcja 1. Uruchamianie kroków bezpośrednio w notesie wykonywalnym za pośrednictwem programu Azure Data Studio

Przed podjęciem próby otwarcia tego notesu sprawdź, czy program Azure Data Studio jest zainstalowany na komputerze lokalnym. Aby zainstalować, przejdź do pozycji Pobierz i zainstaluj program Azure Data Studio.

Otwórz notes w usłudze Azure Data Studio

Opcja 2. Wykonaj kroki ręcznie

W tym artykule omówiono możliwe odpowiedzi na pełny dziennik transakcji i sugeruje, jak go uniknąć w przyszłości.

Gdy dziennik transakcji stanie się pełny, aparat bazy danych programu SQL Server wystawia błąd 9002. Dziennik może być wypełniany, gdy baza danych jest w trybie online lub w trybie odzyskiwania. Jeśli dziennik zostanie zapełniony w czasie, gdy baza danych jest w trybie online, baza danych pozostanie w tym trybie, ale dane mogą być z niej tylko odczytywane, a nie aktualizowane. Jeśli dziennik zostanie wypełniony podczas odzyskiwania, silnik bazy danych oznaczy bazę danych jako RESOURCE PENDING. W obu przypadkach do udostępnienia miejsca w dzienniku jest wymagana akcja użytkownika.

Typowe przyczyny pełnego dziennika transakcji

Odpowiednia odpowiedź na pełny dziennik transakcji zależy od warunków, które spowodowały wypełnienie dziennika. Typowe przyczyny:

  • Rejestr nie jest skrócony
  • Wolumin dysku jest pełny
  • Rozmiar dziennika jest ustawiony na stałą maksymalną wartość lub automatyczny wzrost jest wyłączony.
  • Synchronizacja replikacji lub grupy dostępności, która nie może się zakończyć

Postępuj zgodnie z tymi konkretnymi krokami, aby znaleźć przyczynę pełnego dziennika transakcji i rozwiązać problem.

1. Obcinanie dziennika

Typowym rozwiązaniem tego problemu jest wykonywanie kopii zapasowych dziennika transakcji dla bazy danych, co gwarantuje skrócenie dziennika. Jeśli nie wskazano najnowszej historii dziennika transakcji dla bazy danych z pełnym dziennikiem transakcji, rozwiązanie problemu jest proste: wznawianie regularnych kopii zapasowych dziennika transakcji bazy danych.

Aby uzyskać więcej informacji, zobacz Zarządzanie rozmiarem pliku dziennika transakcji i Zmniejsz plik.

Objaśnienie obcięcia dziennika

Istnieje różnica między przycinaniem dziennika transakcji a zmniejszaniem dziennika transakcji. Obcięcie dziennika odbywa się zwykle podczas tworzenia kopii zapasowej dziennika transakcji i jest operacją logiczną, która usuwa zatwierdzone rekordy w dzienniku, podczas gdy zmniejszanie dziennika odzyskuje fizyczną przestrzeń w systemie plików przez zmniejszenie rozmiaru pliku. Obcinanie dziennika występuje na granicy pliku dziennika wirtualnego (VLF), a plik dziennika może zawierać wiele plików VLF. Plik dziennika może zostać skurczony tylko wtedy, gdy w pliku dziennika jest puste miejsce, które można odzyskać. Zmniejszenie samego pliku dziennika nie może rozwiązać problemu pełnego pliku dziennika. Zamiast tego musisz dowiedzieć się, dlaczego plik dziennika jest pełny i nie można go obcinać.

Ostrzeżenie

Dane przenoszone w celu zmniejszenia pliku mogą być rozproszone do dowolnej dostępnej lokalizacji w pliku. Powoduje to fragmentację indeksu i może spowolnić wydajność zapytań, które wyszukują zakres indeksu. Aby wyeliminować fragmentację, rozważ odbudowę indeksów w pliku po jego zmniejszeniu. Aby uzyskać więcej informacji, zobacz Zmniejszanie bazy danych.

Co uniemożliwia trunkowanie logu?

Aby dowiedzieć się, co uniemożliwia obcięcie dziennika w danym przypadku, użyj kolumn log_reuse_wait i log_reuse_wait_desc widoku katalogu sys.databases. Aby uzyskać więcej informacji, zobacz sys.databases. Opisy czynników, które mogą opóźniać obcinanie dziennika, zobacz Dziennik transakcji.

Poniższy zestaw poleceń języka T-SQL pomaga określić, czy dziennik transakcji bazy danych nie jest obcięty, oraz zidentyfikować przyczynę tego stanu. Poniższy skrypt zaleca również wykonanie kroków w celu rozwiązania problemu:

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;

Ważne

Jeśli baza danych była w trybie odzyskiwania, gdy wystąpił błąd 9002, po rozwiązaniu problemu, odzyskaj bazę danych, używając polecenia ALTER DATABASE database_name SET ONLINE.

LOG_BACKUP log_reuse_wait

Najczęstszą akcją do rozważenia, jeśli widzisz LOG_BACKUP lub log_reuse_wait jest przejrzenie modelu odzyskiwania bazy danych i utworzenie kopii zapasowej dziennika transakcji bazy danych.

Rozważ model odzyskiwania bazy danych

Dziennik transakcji może nie być obcinany z kategorią LOG_BACKUP lub log_reuse_wait, ponieważ nigdy nie wykonano jego kopii zapasowej. W wielu z tych przypadków Twoja baza danych korzysta z modelu odzyskiwania FULL lub BULK_LOGGED, ale nie utworzyłeś kopii zapasowej dziennika transakcji. Należy dokładnie rozważyć każdy model odzyskiwania bazy danych: wykonaj regularne kopie zapasowe dziennika transakcji we wszystkich bazach danych lub FULLBULK_LOGGED modelach odzyskiwania, aby zminimalizować wystąpienia błędu 9002. Aby uzyskać więcej informacji, zobacz Modele odzyskiwania.

Tworzenie kopii zapasowej dziennika

FULL W modelu odzyskiwania lub BULK_LOGGED jeśli ostatnio nie wykonano kopii zapasowej dziennika transakcji, może to być przyczyną uniemożliwiającą usunięcie nadmiarowych danych z dziennika. Należy utworzyć kopię zapasową dziennika transakcji, aby umożliwić wydawanie i obcinanie zapisów dziennika. Jeśli nigdy nie utworzono kopii zapasowej dziennika, należy utworzyć dwie kopie zapasowe dziennika , aby umożliwić aparatowi bazy danych obcięcie dziennika do punktu ostatniej kopii zapasowej. Obcięcie dziennika zwalnia miejsce logiczne dla nowych rekordów dziennika. Aby zapobiec ponownemu wypełnianiu dziennika, należy regularnie i częściej wykonywać kopie zapasowe dziennika. Aby uzyskać więcej informacji, zobacz Modele odzyskiwania.

Pełna historia wszystkich operacji tworzenia i przywracania kopii zapasowych programu SQL Server w wystąpieniu serwera jest przechowywana w systemowej msdb bazie danych. Aby przejrzeć pełną historię kopii zapasowych bazy danych, użyj następującego przykładowego skryptu:

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;

Pełna historia wszystkich operacji tworzenia i przywracania kopii zapasowych programu SQL Server w wystąpieniu serwera jest przechowywana w systemowej msdb bazie danych. Aby uzyskać więcej informacji na temat historii kopii zapasowych, zobacz Historia kopii zapasowych i informacje o nagłówku (SQL Server).

Tworzenie kopii zapasowej dziennika transakcji

Przykład tworzenia kopii zapasowej dziennika:

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

Ważne

Jeśli baza danych jest uszkodzona, zobacz Kopie zapasowe dziennika końcowego (SQL Server).

TRANSAKCJA_AKTYWNA log_ponowne_użycie_oczekiwanie

Kroki rozwiązywania problemu ACTIVE_TRANSACTION obejmują odnalezienie długotrwałej transakcji i jej rozwiązanie (w niektórych przypadkach przy użyciu polecenia KILL).

Odnajdywanie długotrwałych transakcji

Długotrwała transakcja może spowodować wypełnienie dziennika transakcji. Aby wyszukać długotrwałe transakcje, użyj jednej z następujących opcji:

  • sys.dm_tran_database_transactions:

    Ten dynamiczny widok zarządzania zwraca informacje o transakcjach na poziomie bazy danych. W przypadku długotrwałej transakcji kolumny o szczególnym znaczeniu obejmują czas pierwszego rekordu dziennika (database_transaction_begin_time), bieżący stan transakcji () oraz database_transaction_stateLSN) rekordu BEGIN w dzienniku transakcji (database_transaction_begin_lsn).

  • DBCC OPENTRAN:

    To oświadczenie pozwala zidentyfikować identyfikator użytkownika będącego właścicielem transakcji, co umożliwia śledzenie źródła tej transakcji w celu bardziej uporządkowanego zakończenia (zatwierdzenia jej zamiast wycofywania).

Zabij transakcję

Czasami wystarczy zakończyć transakcję; być może trzeba będzie użyć instrukcji KILL . Użyj instrukcji KILL z wyjątkową ostrożnością, szczególnie w przypadku uruchamiania krytycznych procesów, których nie chcesz kończyć.

PUNKT KONTROLNY log_reuse_wait

Żaden punkt kontrolny nie wystąpił od czasu ostatniego obcinania dziennika lub nagłówek dziennika nie został jeszcze przeniesiony poza wirtualny plik dziennika (VLF) we wszystkich modelach odzyskiwania.

Jest to rutynowy powód opóźnienia skracania dziennika. W przypadku opóźnienia rozważ wykonanie polecenia CHECKPOINT w bazie danych lub sprawdzenie dziennika VLFs.

USE dbname;
CHECKPOINT;

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

AVAILABILITY_REPLICA log_reuse_wait (status oczekiwania na ponowne użycie dziennika)

Gdy zmiany transakcji w replice podstawowej zawsze włączonej grupy dostępności nie są jeszcze utrwalone na replice pomocniczej, nie można obciąć dziennika transakcji na podstawowej replice. Może to spowodować wzrost rozmiaru dziennika transakcyjnego i dzieje się tak niezależnie od tego, czy replika pomocnicza jest ustawiona na tryb zatwierdzania synchronicznego lub asynchronicznego. Aby uzyskać informacje na temat rozwiązywania tego typu problemu, zobacz Błąd 9002. Dziennik transakcji bazy danych jest pełny z powodu błędu AVAILABILITY_REPLICA.

Replikacja, śledzenie zmian lub CDC

Funkcje takie jak replikacja, śledzenie zmian i przechwytywanie danych zmian (CDC) polegają na dzienniku transakcji, więc jeśli transakcje lub zmiany nie zostaną przetworzone, może to uniemożliwić obcięcie dziennika transakcji.

Użyj funkcji DBCC OPENTRAN, monitora replikacji lub procedur składowanych na potrzeby śledzenia zmian i usługi CDC , aby zbadać i rozwiązać wszelkie problemy z tymi funkcjami.

Znajdź informacje o czynnikach log_reuse_wait

Aby uzyskać więcej informacji, zobacz Czynniki, które mogą opóźnić przycinanie dziennika.

2. Rozwiązywanie problemów z pełnym woluminem dysku

W niektórych sytuacjach wolumin dysku hostujący plik dziennika transakcji może zostać wypełniony. Aby rozwiązać problem pełnego dziennika wynikający z zapełnionego dysku, możesz wykonać jedną z następujących czynności:

Wolne miejsce na dysku

Możesz zwolnić miejsce na dysku, który zawiera plik dziennika transakcji dla bazy danych, usuwając lub przenosząc inne pliki. Zwolnione miejsce na dysku umożliwia systemowi odzyskiwania automatyczne powiększanie pliku dziennika.

Przenoszenie pliku dziennika na inny dysk

Jeśli nie możesz zwolnić wystarczającej ilości miejsca na dysku, który obecnie zawiera plik dziennika, rozważ przeniesienie pliku na inny dysk z wystarczającą ilością miejsca.

Ważne

Pliki dziennika nigdy nie powinny być umieszczane w skompresowanych systemach plików.

Aby uzyskać informacje na temat zmiany lokalizacji pliku dziennika, zobacz Przenoszenie plików bazy danych .

Dodawanie pliku dziennika na innym dysku

Dodaj nowy plik dziennika do bazy danych na innym dysku, który ma wystarczającą ilość miejsca przy użyciu polecenia ALTER DATABASE <database_name> ADD LOG FILE. Wiele plików dziennika dla pojedynczej bazy danych należy traktować jako stan tymczasowy, aby rozwiązać problem związany z miejscem, nie jako rozwiązanie długoterminowe. Większość baz danych powinna mieć tylko jeden plik dziennika transakcji. Kontynuuj badanie przyczyny, dla którego dziennik transakcji jest pełny i nie można go obcinać. Rozważ dodanie dodatkowych tymczasowych plików dziennika transakcji tylko jako zaawansowany krok rozwiązywania problemów.

Aby uzyskać więcej informacji, zobacz Dodawanie danych lub plików dziennika do bazy danych.

Te kroki można częściowo zautomatyzować, uruchamiając następujący skrypt języka T-SQL w celu zidentyfikowania plików dzienników korzystających z dużej części miejsca na dysku i sugerowanych akcji:

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. Zmienianie limitu rozmiaru dziennika lub włączanie automatycznego zwiększania

Błąd 9002 można wygenerować, jeśli rozmiar dziennika transakcji został ustawiony na górny limit lub funkcja automatycznego zwiększania nie jest dozwolona. W takim przypadku włączenie automatycznego zwiększania rozmiaru lub ręczne zwiększenie rozmiaru dziennika może pomóc rozwiązać ten problem. Użyj tego polecenia języka T-SQL, aby znaleźć takie pliki dziennika i postępować zgodnie z podanymi zaleceniami:

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;

Zwiększanie rozmiaru pliku dziennika lub włączanie automatycznego zwiększania

Jeśli miejsce jest dostępne na dysku dziennika, możesz zwiększyć rozmiar pliku dziennika. Maksymalny rozmiar plików dziennika to 2 terabajty (TB) na plik dziennika.

Jeśli funkcja automatycznego zwiększania jest wyłączona, baza danych jest w trybie online i na dysku jest dostępna wystarczająca ilość miejsca, rozważ wykonanie następujących czynności:

Uwaga / Notatka

W obu przypadkach, jeśli zostanie osiągnięty bieżący limit rozmiaru, zwiększ MAXSIZE wartość.

Powiązana treść