Bagikan melalui

Memecahkan masalah log transaksi penuh (Kesalahan SQL Server 9002)

Berlaku untuk:SQL Server

Artikel ini berlaku untuk instans SQL Server.

Catatan

Artikel ini difokuskan pada SQL Server. Untuk informasi lebih spesifik tentang kesalahan ini di platform Azure SQL, lihat Memecahkan masalah kesalahan log transaksi dengan Azure SQL Database dan Memecahkan masalah kesalahan log transaksi dengan Azure SQL Managed Instance. Azure SQL Database dan Azure SQL Managed Instance didasarkan pada versi stabil terbaru mesin database Microsoft SQL Server, sehingga banyak konten serupa meskipun opsi dan alat pemecahan masalah mungkin berbeda.

Opsi 1: Jalankan langkah-langkah langsung di buku catatan yang dapat dieksekusi melalui Azure Data Studio

Sebelum mencoba membuka buku catatan ini, periksa apakah Azure Data Studio diinstal di komputer lokal Anda. Untuk menginstal, buka Mengunduh dan menginstal Azure Data Studio.

Buka Notebook di Azure Data Studio

Opsi 2: Ikuti langkah-langkah secara manual

Artikel ini membahas kemungkinan respons terhadap log transaksi penuh dan menyarankan cara menghindarinya di masa mendatang.

Ketika log transaksi menjadi penuh, Mesin Database SQL Server mengeluarkan kesalahan 9002. Log dapat mengisi saat database online, atau dalam pemulihan. Jika log terisi saat database online, database tetap online tetapi hanya dapat dibaca, bukan diperbarui. Jika log terisi selama pemulihan, Mesin Database menandai database sebagai RESOURCE PENDING. Dalam kedua kasus, tindakan pengguna diperlukan untuk membuat ruang log tersedia.

Alasan umum untuk log transaksi penuh

Respons yang sesuai terhadap log transaksi penuh tergantung pada kondisi apa yang menyebabkan log terisi. Penyebab umumnya meliputi:

  • Log tidak dipotong
  • Volume disk penuh
  • Ukuran log diatur ke nilai maksimum tetap atau pertumbuhan otomatis dinonaktifkan
  • Replikasi atau sinkronisasi grup ketersediaan yang tidak dapat diselesaikan

Ikuti langkah-langkah spesifik ini untuk membantu Anda menemukan alasan log transaksi lengkap dan menyelesaikan masalah.

1. Pangkas catatan

Solusi umum untuk masalah ini adalah memastikan pencadangan cadangan log transaksi dilakukan untuk database Anda, yang memastikan pemendekan log. Jika tidak ada riwayat log transaksi terbaru yang ditunjukkan untuk database dengan log transaksi penuh, solusi untuk masalahnya mudah: lanjutkan pencadangan log transaksi reguler database.

Untuk informasi selengkapnya, tinjau Mengelola ukuran file log transaksi dan Mengurangi ukuran file.

Pemotongan log dijelaskan

Ada perbedaan antara memotong log transaksi dan menyusutkan log transaksi. Pemotongan log terjadi secara normal selama pencadangan log transaksi, dan merupakan operasi logis yang menghapus rekaman yang diterapkan di dalam log, sedangkan penyusutan log mengklaim kembali ruang fisik pada sistem file dengan mengurangi ukuran file. Pemotongan log terjadi pada batas file log virtual (VLF), dan file log mungkin berisi banyak VLF. File log dapat disusutkan hanya jika ada ruang kosong di dalam file log untuk diklaim kembali. Menyusutkan file log saja tidak dapat menyelesaikan masalah file log lengkap. Sebagai gantinya, Anda harus menemukan mengapa file log penuh dan tidak dapat dipotong.

Peringatan

Data yang dipindahkan untuk menyusutkan file dapat tersebar ke lokasi yang tersedia dalam file. Ini menyebabkan fragmentasi indeks dan mungkin memperlambat performa kueri yang mencari rentang indeks. Untuk menghilangkan fragmentasi, pertimbangkan untuk membangun kembali indeks pada file setelah menyusut. Untuk informasi selengkapnya, lihat Menyusutkan database.

Apa yang mencegah pemotongan log?

Untuk menemukan apa yang mencegah pemotongan log dalam kasus tertentu, gunakan log_reuse_wait kolom log_reuse_wait_desc dan sys.databases tampilan katalog. Untuk informasi selengkapnya, lihat sys.databases. Untuk deskripsi faktor-faktor yang dapat menunda pemotongan log, lihat Log transaksi.

Sekumpulan perintah T-SQL berikut membantu Anda mengidentifikasi apakah log transaksi database tidak terpotong dan alasannya. Skrip berikut ini juga merekomendasikan langkah-langkah untuk mengatasi masalah:

SET NOCOUNT ON;

DECLARE
    @SQL AS VARCHAR (8000),
    @log_reuse_wait AS TINYINT,
    @log_reuse_wait_desc AS NVARCHAR (120),
    @dbname AS SYSNAME,
    @database_id AS INT,
    @recovery_model_desc AS VARCHAR (24);

IF (OBJECT_id(N'tempdb..#CannotTruncateLog_Db') IS NOT NULL)
    BEGIN
        DROP TABLE #CannotTruncateLog_Db;
    END

--get info about transaction logs in each database.
IF (OBJECT_id(N'tempdb..#dm_db_log_space_usage') IS NOT NULL)
    BEGIN
        DROP TABLE #dm_db_log_space_usage;
    END

SELECT *
INTO #dm_db_log_space_usage
FROM sys.dm_db_log_space_usage
WHERE 1 = 0;

DECLARE log_space CURSOR
    FOR SELECT NAME
        FROM sys.databases;

OPEN log_space;

FETCH NEXT FROM log_space INTO @dbname;

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = '
            INSERT INTO #dm_db_log_space_usage (
                database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
                )
            SELECT database_id,
                total_log_size_in_bytes,
                used_log_space_in_bytes,
                used_log_space_in_percent,
                log_space_in_bytes_since_last_backup
            FROM ' + QUOTENAME(@dbname) + '.sys.dm_db_log_space_usage;';
        BEGIN TRY
            EXECUTE (@SQL);
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE() AS ErrorMessage;
        END CATCH
        FETCH NEXT FROM log_space INTO @dbname;
    END

CLOSE log_space;
DEALLOCATE log_space;

--select the affected databases
SELECT
    sdb.name AS DbName,
    sdb.log_reuse_wait,
    sdb.log_reuse_wait_desc,
    CASE
        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a deferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
        ELSE 'None'
    END AS log_reuse_wait_explanation,
    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024. / 1024. AS Used_log_size_MB,
    lsu.total_log_size_in_bytes / 1024. / 1024. AS Total_log_size_MB,
    100 - lsu.used_log_space_in_percent AS Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb
     INNER JOIN #dm_db_log_space_usage AS lsu
         ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0;

SELECT * FROM #CannotTruncateLog_Db;

DECLARE no_truncate_db CURSOR FOR
SELECT
    log_reuse_wait,
    log_reuse_wait_desc,
    DbName,
    database_id,
    recovery_model_desc
FROM #CannotTruncateLog_Db;

OPEN no_truncate_db;

FETCH NEXT FROM no_truncate_db
INTO
    @log_reuse_wait,
    @log_reuse_wait_desc,
    @dbname,
    @database_id,
    @recovery_model_desc;

WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@log_reuse_wait > 0)
        BEGIN
            SELECT '-- ' + QUOTENAME(@dbname) + ' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --' AS 'Individual Database Report';
        END
        IF (@log_reuse_wait = 1)
        BEGIN
            SELECT 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' AS Recommendation;
            SELECT 'USE ' + QUOTENAME(@dbname) + '; CHECKPOINT' AS CheckpointCommand;
            SELECT 'SELECT * FROM sys.dm_db_log_info(' + CONVERT (VARCHAR, @database_id) + ')' AS VLF_LogInfo;
        END
        ELSE IF (@log_reuse_wait = 2)
        BEGIN
            SELECT 'Is ' + @recovery_model_desc + ' recovery model the intended choice for ' + QUOTENAME(@dbname) + ' database? Review recovery models and determine if you need to change it. https://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;

Penting

Jika database dalam pemulihan ketika kesalahan 9002 terjadi, setelah menyelesaikan masalah, pulihkan database dengan menggunakan ALTER DATABASE database_name SET ONLINE.

Cadangan_Log tunggu_penggunaan_kembali_log

Tindakan paling umum yang perlu dipertimbangkan jika Anda melihat LOG_BACKUP atau log_reuse_wait adalah meninjau model pemulihan database Anda, dan mencadangkan log transaksi dari database Anda.

Pertimbangkan model pemulihan database

Log transaksi mungkin gagal dipotong dengan LOG_BACKUP atau log_reuse_wait kategori, karena Anda belum pernah mencadangkannya. Dalam banyak kasus tersebut, database Anda menggunakan model pemulihan FULL atau BULK_LOGGED, tetapi Anda tidak mencadangkan log transaksi Anda. Anda harus mempertimbangkan setiap model pemulihan database dengan hati-hati: melakukan pencadangan log transaksi reguler pada semua database dalam FULL atau BULK_LOGGED model pemulihan, untuk meminimalkan terjadinya kesalahan 9002. Untuk informasi selengkapnya, lihat Model pemulihan.

Mencadangkan log

Di bawah model pemulihan FULL atau BULK_LOGGED, jika log transaksi belum dicadangkan baru-baru ini, cadangan tersebut mungkin yang mencegah pemotongan log. Anda harus mencadangkan log transaksi untuk mengizinkan rekaman log dirilis dan log terpotong. Jika log belum pernah dicadangkan, Anda harus membuat dua cadangan log untuk mengizinkan Mesin Database memotong log ke titik cadangan terakhir. Memotong log membebaskan ruang logika untuk rekaman log baru. Agar log tidak terisi lagi, ambil cadangan log secara teratur dan lebih sering. Untuk informasi selengkapnya, lihat Model pemulihan.

Riwayat lengkap semua operasi pencadangan dan pemulihan SQL Server pada instans server disimpan dalam msdb database sistem. Untuk meninjau riwayat pencadangan lengkap database, gunakan contoh skrip berikut:

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;

Riwayat lengkap semua operasi pencadangan dan pemulihan SQL Server pada instans server disimpan dalam msdb database sistem. Untuk informasi selengkapnya tentang riwayat pencadangan, lihat Riwayat Pencadangan dan Informasi Header (SQL Server).

Membuat cadangan log transaksi

Contoh cara mencadangkan log:

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

Penting

Jika database rusak, lihat Pencadangan log akhir (SQL Server).

TRANSAKSI_AKTIF tunggu_penggunaan_kembali_log

Langkah-langkah untuk memecahkan masalah ACTIVE_TRANSACTION penyebab termasuk menemukan transaksi yang berlangsung lama dan menyelesaikannya (dalam beberapa kasus menggunakan perintah KILL untuk melakukannya).

Temukan transaksi jangka panjang

Transaksi yang berjalan lama dapat menyebabkan log transaksi terisi. Untuk mencari transaksi yang berjalan lama, gunakan salah satu opsi berikut:

  • sys.dm_tran_database_transactions:

    Tampilan manajemen dinamis ini mengembalikan informasi terkait transaksi di tingkat database. Untuk transaksi yang berjalan lama, kolom minat tertentu mencakup waktu rekaman log pertama (database_transaction_begin_time), status transaksi saat ini (database_transaction_state), dan nomor urutan log (LSN) dari BEGIN rekaman dalam log transaksi (database_transaction_begin_lsn).

  • DBCC OPENTRAN:

    Pernyataan ini memungkinkan Anda mengidentifikasi ID pengguna pemilik transaksi, sehingga Anda dapat berpotensi melacak sumber transaksi untuk penghentian yang lebih terarah (menerapkan dan bukannya mengambil kembali).

Menghentikan transaksi

Terkadang Anda hanya perlu mengakhiri transaksi; Anda mungkin harus menggunakan pernyataan KILL . KILL Gunakan pernyataan dengan sangat hati-hati, terutama ketika proses penting berjalan yang tidak ingin Anda akhiri.

log_reuse_wait CHECKPOINT

Tidak ada titik pemeriksaan yang terjadi sejak pemotongan log terakhir, atau kepala log belum bergerak melampaui file log virtual (VLF), di semua model pemulihan.

Ini adalah alasan rutin untuk menunda pemotongan log. Jika tertunda, pertimbangkan untuk menjalankan CHECKPOINT perintah pada database atau memeriksa VLF log.

USE dbname;
CHECKPOINT;

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

Replika_Ketersediaan log_tunggu_penggunaan_kembali

Ketika transaksi berubah di replika grup ketersediaan AlwaysOn utama belum diperkuat pada replika sekunder, log transaksi replika utama tidak dapat dipotong. Ini dapat menyebabkan log tumbuh, dan terjadi apakah replika sekunder diatur untuk mode penerapan sinkron atau asinkron. Untuk informasi tentang cara memecahkan masalah jenis ini, lihat Kesalahan 9002. Log transaksi untuk database penuh karena kesalahan AVAILABILITY_REPLICA.

Replikasi, pelacakan perubahan, atau CDC

Fitur seperti replikasi, pelacakan perubahan, dan penangkapan data perubahan (CDC) mengandalkan log transaksi, jadi jika transaksi atau perubahan tidak dikirimkan, itu dapat mencegah log transaksi terpotong.

Gunakan DBCC OPENTRAN, Replication Monitor, atau prosedur tersimpan untuk pelacakan perubahan dan CDC untuk menyelidiki dan menyelesaikan masalah apa pun dengan fitur-fitur ini.

Temukan informasi tentang faktor log_reuse_wait

Untuk informasi selengkapnya, lihat Faktor-faktor yang dapat menunda pemotongan log.

2. Mengatasi volume disk penuh

Dalam beberapa situasi, volume disk yang menghosting file log transaksi mungkin terisi. Anda dapat mengambil salah satu tindakan berikut untuk menyelesaikan skenario penuh log yang dihasilkan dari disk lengkap:

Ruang disk kosong

Anda mungkin dapat membebaskan ruang disk pada disk drive yang berisi file log transaksi untuk database dengan menghapus atau memindahkan file lain. Ruang disk yang dikosongkan memungkinkan sistem pemulihan untuk memperbesar file log secara otomatis.

Memindahkan file log ke disk yang berbeda

Jika Anda tidak dapat mengosongkan ruang disk yang cukup pada drive yang saat ini berisi file log, pertimbangkan untuk memindahkan file ke drive lain dengan ruang yang memadai.

Penting

File log tidak boleh ditempatkan pada sistem file terkompresi.

Lihat Memindahkan file database untuk informasi tentang cara mengubah lokasi file log.

Menambahkan file log pada disk yang berbeda

Tambahkan file log baru ke database pada disk lain yang memiliki ruang yang cukup dengan menggunakan ALTER DATABASE <database_name> ADD LOG FILE. Beberapa file log untuk database tunggal harus dianggap sebagai kondisi sementara untuk mengatasi masalah ruang, bukan kondisi jangka panjang. Sebagian besar database hanya boleh memiliki satu file log transaksi. Lanjutkan untuk menyelidiki alasan mengapa log transaksi penuh dan tidak dapat dipotong. Pertimbangkan untuk menambahkan file log transaksi sementara tambahan hanya sebagai langkah pemecahan masalah tingkat lanjut.

Untuk informasi selengkapnya, lihat Menambahkan Data atau File Log ke Database.

Langkah-langkah ini dapat diotomatisasi sebagian dengan menjalankan skrip T-SQL berikut untuk mengidentifikasi file log yang menggunakan persentase besar ruang disk dan menyarankan tindakan:

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. Ubah batas ukuran log atau aktifkan autogrow

Kesalahan 9002 dapat dihasilkan jika ukuran log transaksi diatur ke batas atas, atau fitur autogrow tidak diizinkan. Dalam hal ini, mengaktifkan pertumbuhan otomatis atau meningkatkan ukuran log secara manual dapat membantu menyelesaikan masalah. Gunakan perintah T-SQL ini untuk menemukan file log tersebut dan ikuti rekomendasi yang diberikan:

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;

Meningkatkan ukuran file log atau mengaktifkan pertumbuhan otomatis

Jika ruang tersedia di disk log, Anda dapat meningkatkan ukuran file log. Ukuran maksimum untuk file log adalah 2 terabyte (TB) per file log.

Jika autogrow dinonaktifkan, database sedang online, dan ruang yang cukup tersedia di disk, pertimbangkan untuk mengambil langkah-langkah berikut:

Catatan

Dalam kedua kasus, jika batas ukuran saat ini tercapai, tingkatkan nilainya MAXSIZE .

Konten terkait

  • Last updated on