Aracılığıyla paylaş

Tam işlem günlüğü sorunlarını giderme (SQL Server Hatası 9002)

Şunlar için geçerlidir: SQL Server

Bu makale SQL Server örnekleri için geçerlidir.

Uyarı

Bu makale SQL Server'a odaklanmıştır. Azure SQL platformlarında bu hata hakkında daha ayrıntılı bilgi için bkz. Azure SQL Veritabanı ile işlem günlüğü hatalarını giderme ve Azure SQL Yönetilen Örneği ile ilgili işlem günlüğü hatalarını giderme. Azure SQL Veritabanı ve Azure SQL Yönetilen Örneği, Microsoft SQL Server veritabanı altyapısının en son kararlı sürümünü temel alır, bu nedenle içeriğin çoğu benzerdir, ancak sorun giderme seçenekleri ve araçları farklılık gösterebilir.

1. Seçenek: Azure Data Studio aracılığıyla adımları doğrudan yürütülebilir bir not defterinde çalıştırın

Bu not defterini açmaya çalışmadan önce Yerel makinenizde Azure Data Studio'yu yükleyip yüklemediğini denetleyin. Yüklemek için Azure Data Studio'yu indirme ve yükleme bölümüne gidin.

Azure Data Studio'da Not Defteri'ni açma

2. Seçenek: Adımları el ile izleyin

Bu makalede, tam işlem günlüğüne verilen olası yanıtlar ele alınmaktadır ve gelecekte bu günlükten nasıl kaçınılacağı açıklanır.

İşlem günlüğü dolduğunda, SQL Server Veritabanı Altyapısı bir 9002 hatası gönderir. Veritabanı çevrimiçi olduğunda veya kurtarma sırasında günlük doldurulabilir. Veritabanı çevrimiçi durumdayken günlük dolarsa, veritabanı çevrimiçi kalır ama yalnızca okunabilir, güncelleştirilemez. Kurtarma sırasında günlük dolarsa, Veritabanı Altyapısı veritabanını RESOURCE PENDING olarak işaretler. Her iki durumda da, günlük alanını kullanılabilir hale getirmek için kullanıcının bir işlem yapması gerekir.

Tam işlem günlüğünün yaygın nedenleri

Tam işlem günlüğüne uygun yanıt, günlüğün doldurulmasına neden olan koşullara bağlıdır. Yaygın nedenler şunlardır:

  • Günlük kesilmiyor
  • Disk birimi dolu
  • Günlük boyutu sabit bir maksimum değere ayarlanır veya otomatik büyütme devre dışı bırakılır
  • Tamamlanamayan replikasyon veya kullanılabilirlik grubu senkronizasyonu

Tam işlem günlüğünün nedenini bulmanıza ve sorunu çözmenize yardımcı olması için bu belirli adımları izleyin.

1. Günlüğü kısalt

Bu sorunun yaygın bir çözümü, veritabanınızın işlem günlüğü yedeklemelerinin yapıldığından emin olmaktır; bu da günlüğün kısaltılmasını sağlar. Tam işlem günlüğü olan veritabanı için son işlem günlüğü geçmişi belirtilmemişse, sorunun çözümü basittir: veritabanının normal işlem günlüğü yedeklemelerini sürdürme.

Daha fazla bilgi için İşlem günlüğü dosyasının boyutunu yönetme ve Dosyayı küçültme makalesini gözden geçirin.

Log kesme işlemi açıklandı

İşlem günlüğünü kesmek ile işlem günlüğünü küçültmek arasında fark vardır. Günlük kesilmesi normalde bir işlem günlüğü yedeklemesi sırasında gerçekleşir ve günlüğün içindeki kaydedilmiş kayıtları kaldıran mantıksal bir işlemdir, ancak günlük daraltma dosya boyutunu azaltarak dosya sistemindeki fiziksel alanı geri alır. Günlük dosyasının kesilmesi, bir sanal günlük dosyası (VLF) sınırında gerçekleşir ve bir günlük dosyası birçok VLF içerebilir. Günlük dosyasının daraltılması için günlük dosyasının içinde geri kazanılacak boş alan olması gerekir. Günlük dosyasının tek başına küçültülmesi, tam günlük dosyasının sorununu çözemez. Bunun yerine, günlük dosyasının neden dolu olduğunu ve neden kesilemediğini bulmanız gerekir.

Uyarı

Dosyayı küçültmek için taşınan veriler, dosyadaki kullanılabilir herhangi bir konuma dağıtılabilir. Bu, dizin parçalanmasına neden olur ve dizin aralığını arayan sorguların performansını yavaşlatabilir. Parçalanmayı ortadan kaldırmak için, daraltıldıktan sonra dosyadaki dizinleri yeniden oluşturmayı göz önünde bulundurun. Daha fazla bilgi için bkz. veritabanını küçültme.

Günlük kesilmesini engelleyen nedir?

Belirli bir durumda günlük kesilmesini engelleyen öğeleri bulmak için katalog görünümünün log_reuse_waitlog_reuse_wait_desc ve sys.databases sütunlarını kullanın. Daha fazla bilgi için bkz. sys.databases. Günlük kesilmesini geciktirebilecek faktörlerin açıklamaları için bkz. İşlem günlüğü.

Aşağıdaki T-SQL komutları kümesi, veritabanı işlem günlüğünün kesilmediğini ve bunun nedenini belirlemenize yardımcı olur. Aşağıdaki betik, sorunu çözme adımlarını da önerir:

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;

Önemli

9002 hatası oluştuğunda veritabanı kurtarma aşamasındaysa, sorunu çözdükten sonra ALTER DATABASE database_name SET ONLINE kullanarak veritabanını kurtarın.

LOG_BACKUP log_reuse_wait

Eğer LOG_BACKUP veya log_reuse_wait görüyorsanız, göz önünde bulundurmanız gereken en yaygın eylem, veritabanı kurtarma modelinizi gözden geçirmek ve veritabanınızın işlem günlüğünü yedeklemektir.

Veritabanının kurtarma modelini göz önünde bulundurun

işlem günlüğü, hiçbir zaman yedeklememiş olduğunuz için LOG_BACKUP veya log_reuse_wait kategorisiyle kesilemiyor olabilir. Bu durumların çoğunda, veritabanınız FULL ya da BULK_LOGGED kurtarma modelini kullanıyor, ancak işlem günlüğünüzü yedeklememişsiniz. Her bir veritabanı kurtarma modelini dikkatlice değerlendirmelisiniz: Hata 9002'nin oluşumlarını en aza indirmek için, FULL veya BULK_LOGGED kurtarma modellerindeki tüm veritabanlarında düzenli işlem günlüğü yedeklemeleri yapın. Daha fazla bilgi için bkz. Kurtarma modelleri.

Günlüğü yedekleme

FULL veya BULK_LOGGED kurtarma modeli altında, işlem günlüğü yakın zamanda yedeklenmediyse, yedekleme günlüğün kesilmesini engelleyen şey olabilir. Günlük kayıtlarının serbest bırakılmasına ve günlüğün kısaltılmasına izin vermek için işlem günlüğünü yedeklemeniz gerekir. Günlük daha önce hiç yedeklenmediyse, Veritabanı Motoru'nun günlüğü son yapılan yedekleme noktasına kadar kesebilmesi için iki log yedeği oluşturmanız gereklidir. Günlüğün kesilmesi, yeni günlük kayıtları için mantıksal alan açar. Günlüğün yeniden dolmaması için günlük yedeklerini düzenli ve daha sık alın. Daha fazla bilgi için bkz. Kurtarma modelleri.

Sunucu örneğindeki tüm SQL Server yedekleme ve geri yükleme işlemlerinin msdb tam geçmişi sistem veritabanında depolanır. Bir veritabanının yedekleme geçmişinin tamamını gözden geçirmek için aşağıdaki örnek betiği kullanın:

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;

Sunucu örneğindeki tüm SQL Server yedekleme ve geri yükleme işlemlerinin msdb tam geçmişi sistem veritabanında depolanır. Yedekleme geçmişi hakkında daha fazla bilgi için bkz . Yedekleme Geçmişi ve Üst Bilgi Bilgileri (SQL Server).

İşlem günlüğü yedeklemesi oluşturma

Günlüğü yedekleme örneği:

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

Önemli

Veritabanı zarar görmüşse bkz. Tail-log yedeklemeleri (SQL Server).

AKTİF_İŞLEM günlük_yeniden_kullanım_bekleme

Sorunun nedenini giderme adımları, uzun süre çalışan işlemi keşfetmeyi ve çözmeyi (bazı durumlarda ACTIVE_TRANSACTION komutunu kullanarak) içerir.

Uzun süreli işlemleri keşfetme

Uzun süre çalışan bir işlem işlem günlüğünün dolmasına neden olabilir. Uzun süre çalışan işlemleri aramak için aşağıdaki seçeneklerden birini kullanın:

  • sys.dm_tran_database_transactions:

    Bu dinamik yönetim görünümü, veritabanı düzeyindeki işlemler hakkında bilgi döndürür. Uzun süreli bir işlem için, ilgi çekici sütunlar arasında ilk günlük kaydının (database_transaction_begin_time) zamanı, işlemin mevcut durumu (database_transaction_state) ve işlem günlüğündeki kaydın () günlük sırası numarası (LSN) (BEGIN) bulunur.

  • DBCC OPENTRAN:

    Bu açıklama, işlemin sahibinin kullanıcı kimliğini belirlemenize olanak tanır, böylece işlemin kaynağını izleyerek onu geri almak yerine onaylayarak daha düzenli bir şekilde sonlandırabilirsiniz.

İşlemi sonlandırma

Bazen yalnızca işlemi sonlandırmanız gerekir; KILL deyimini kullanmanız gerekebilir. Özellikle sonlandırmak istemediğiniz kritik işlemler çalışırken, ifadeyi KILL son derece dikkatli kullanın.

KONTROL NOKTASI log_reuse_wait

Son günlük kesilmesinden bu yana hiçbir denetim noktası oluşmadı veya günlüğün başı tüm kurtarma modellerinde sanal günlük dosyasının (VLF) ötesine henüz taşınmadı.

Bu, günlüklerin kesilmesini geciktirmenin rutin bir nedenidir. Gecikirse, komutu veritabanında yürütmeyi CHECKPOINT veya günlük VLF'lerini incelemeyi göz önünde bulundurun.

USE dbname;
CHECKPOINT;

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

AVAILABILITY_REPLICA günlük_yeniden_kullanım_bekleme

Birincil Always On kullanılabilirlik grubu çoğaltması üzerindeki işlem değişiklikleri henüz ikincil çoğaltmada sağlamlaştırılmadığında, birincil çoğaltma işlem günlüğü kesilemez. Bu, günlüğün büyümesine neden olabilir ve bu durum, ikincil çoğaltmanın zaman uyumlu veya zaman uyumsuz taahhüt modu için ayarlanmış olmasından bağımsız olarak gerçekleşir. Bu tür sorunları giderme hakkında bilgi için bkz . Hata 9002. veritabanı için işlem günlüğü AVAILABILITY_REPLICA hatası nedeniyle dolu.

Çoğaltma, değişiklik izleme veya Değişiklik Verisi Yakalama (CDC)

Çoğaltma, değişiklik izleme ve değişiklik verileri yakalama (CDC) gibi özellikler işlem günlüğüne dayanır, bu nedenle işlemler veya değişiklikler teslim edilmezse işlem günlüğünün kesilmesini engelleyebilir.

Değişiklik izleme ve CDC için DBCC OPENTRAN, Çoğaltma İzleyicisi veya saklı yordamları kullanarak bu özelliklerle ilgili sorunları araştırın ve çözün.

log_reuse_wait faktörleri hakkında bilgi bulma

Daha fazla bilgi için bkz. Günlük kesilmesini geciktirebilecek faktörler.

2. Tam disk hacmini düzeltme

Bazı durumlarda, işlem günlüğü dosyasını barındıran disk birimi dolabilir. Tam diskten kaynaklanan günlük dolu senaryoyu çözmek için aşağıdaki eylemlerden birini gerçekleştirebilirsiniz:

Boş disk alanı

Diğer dosyaları silerek veya taşıyarak veritabanı için işlem günlüğü dosyasını içeren disk sürücüsünde disk alanı boşaltabilirsiniz. Boş disk alanı, kurtarma sisteminin günlük dosyasını otomatik olarak büyütmesine olanak tanır.

Günlük dosyasını farklı bir diske taşıma

Şu anda günlük dosyasını içeren sürücüde yeterli disk alanı boşaltamıyorsanız, dosyayı yeterli alana sahip başka bir sürücüye taşımayı göz önünde bulundurun.

Önemli

Günlük dosyaları hiçbir zaman sıkıştırılmış dosya sistemlerine yerleştirilmemelidir.

Günlük dosyasının konumunu değiştirme hakkında bilgi için bkz. Veritabanı dosyalarını taşıma .

Farklı bir diske günlük dosyası ekleme

kullanarak ALTER DATABASE <database_name> ADD LOG FILEyeterli alana sahip farklı bir disk üzerindeki veritabanına yeni bir günlük dosyası ekleyin. Tek bir veritabanı için birden çok log dosyası, uzun vadeli değil, alan sorununu çözmek için geçici bir durum olarak kabul edilmelidir. Çoğu veritabanında yalnızca bir işlem günlüğü dosyası olmalıdır. İşlem günlüğünün dolu olmasının ve kırpılamama nedenini araştırmaya devam edin. Yalnızca gelişmiş bir sorun giderme adımı olarak ek geçici işlem günlüğü dosyaları eklemeyi göz önünde bulundurun.

Daha fazla bilgi için bkz. Veritabanına Veri veya Günlük Dosyası Ekleme.

Bu adımlar, disk alanının büyük bir yüzdesini kullanan günlük dosyalarını tanımlamak ve eylem önermek için aşağıdaki T-SQL betiği çalıştırılarak kısmen otomatikleştirilebilir:

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. Günlük boyutu sınırını değiştirme veya otomatik büyütmeyi etkinleştirme

İşlem günlüğü boyutu üst sınıra ayarlandıysa veya otomatik büyütme özelliğine izin verilmiyorsa 9002 hatası oluşturulabilir. Bu durumda, otomatik büyütmeyi etkinleştirmek veya günlük boyutunu el ile artırmak sorunu çözmeye yardımcı olabilir. Bu tür günlük dosyalarını bulmak ve sağlanan önerileri izlemek için bu T-SQL komutunu kullanın:

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;

Günlük dosyası boyutunu artırın veya otomatik büyütmeyi etkinleştirin

Günlük diskinde yer varsa, günlük dosyasının boyutunu artırabilirsiniz. Günlük dosyalarının boyut üst sınırı günlük dosyası başına 2 terabayttır (TB).

Otomatik büyütme devre dışı bırakılırsa, veritabanı çevrimiçidir ve diskte yeterli alan varsa aşağıdaki adımları uygulamayı göz önünde bulundurun:

Uyarı

Her iki durumda da geçerli boyut sınırına ulaşılırsa değeri artırın MAXSIZE .

İlgili içerik