SQL Server Transaction Log Çok mu Büyük? Nedenleri, Teşhisi ve Güvenli Çözümü

Bir SQL Server veritabanının log dosyası (.ldf) veri dosyalarından büyükse ya da diski doldurmaya başladıysa, bu genellikle bir şeylerin yolunda gitmediğinin işaretidir. Çoğu DBA’in ilk refleksi log dosyasını küçültmek (shrink) olur. Ancak log’un neden büyüdüğünü anlamadan yapılan bir shrink, sorunu yalnızca birkaç gün erteler; bazı durumlarda ise backup zincirini kırarak daha büyük bir riske yol açar.

Bu yazıda SQL Server transaction log’unun neden büyüdüğünü, asıl nedenin nasıl teşhis edileceğini, log’un güvenli şekilde nasıl küçültüleceğini ve doğru boyut ile autogrowth ayarlarıyla sorunun nasıl kalıcı olarak önleneceğini adım adım anlatıyoruz.

Önce Temel Kavram: Truncation ve Shrink Farklı Şeylerdir

Transaction log sorunlarının çoğu bu iki kavramın karıştırılmasından kaynaklanır:

• Log truncation (temizleme): log dosyasının içindeki, artık ihtiyaç duyulmayan bölümün yeniden kullanılabilir hale gelmesidir. Mantıksal bir işlemdir; dosyanın diskteki boyutu değişmez. Simple recovery model’de checkpoint sonrasında, full ve bulk-logged modelde ise log backup sonrasında otomatik olarak gerçekleşir.
• Log shrink (küçültme): log dosyasının sonundaki boş alanı işletim sistemine geri vererek dosyanın fiziksel boyutunu düşürür. Yalnızca dosyanın içinde boş alan varsa işe yarar.

Transaction Log Neden Büyür?

Log, temizlenemediği sürece büyümeye devam eder. SQL Server, log’un neden temizlenemediğini her veritabanı için sys.databases görünümündeki log_reuse_wait_desc sütununda açıkça söyler. En sık karşılaşılan değerler şunlardır:

Bunlara ek olarak log, tek seferlik büyük işlemler sırasında da büyür: büyük bir index rebuild, milyonlarca satırı tek transaction’da güncelleyen bir batch ya da veri yükleme işlemi. Böyle bir işlem bittiğinde log temizlenir ama dosya büyümüş halde kalır. Bu durumda log_reuse_wait_desc genellikle NOTHING gösterir ve log’un büyük kısmı boştur.

Adım 1: Hangi Veritabanlarının Log’u Sorunlu?

Aşağıdaki sorgu, sunucudaki tüm veritabanları için log boyutunu, log’un ne kadarının dolu olduğunu, veri dosyalarının toplam boyutunu ve log’un neden temizlenemediğini tek tabloda gösterir:

DECLARE @logspace TABLE (
    database_name sysname,
    log_size_mb   decimal(18, 2),
    log_used_pct  decimal(9, 2),
    status        int
);

INSERT INTO @logspace
EXEC (N'DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');

SELECT d.name,
       d.recovery_model_desc,
       d.log_reuse_wait_desc,
       ls.log_size_mb,
       ls.log_used_pct,
       df.data_size_mb
FROM sys.databases AS d
JOIN @logspace AS ls ON ls.database_name = d.name
CROSS APPLY (SELECT SUM(CAST(mf.size AS bigint)) * 8 / 1024 AS data_size_mb
             FROM sys.master_files AS mf
             WHERE mf.database_id = d.database_id
               AND mf.type = 0) AS df
ORDER BY ls.log_size_mb DESC;

Sonucu şöyle yorumlayın:

• Log büyük ve dolu (log_used_pct yüksek): bir şey log’un temizlenmesini engelliyor. log_reuse_wait_desc değerine göre aşağıdaki çözümü uygulayın. Bu durumda shrink işe yaramaz.
• Log büyük ama büyük kısmı boş (log_used_pct düşük): log geçmişte tek seferlik bir işlem nedeniyle büyümüş. Tekrar büyümesi beklenmiyorsa kontrollü bir shrink yapılabilir.
• Log, veri dosyalarından büyük: tek başına her zaman hata değildir, ancak hemen her zaman incelenmeyi hak eder.

Tek bir veritabanının içinden daha ayrıntılı bilgi almak için sys.dm_db_log_space_usage görünümünü de kullanabilirsiniz. Bu görünüm, son log backup’tan bu yana üretilen log miktarını da gösterir.

Adım 2: Nedene Göre Çözüm

LOG_BACKUP: log backup alınmıyor

Full recovery model’deki bir veritabanında log, ancak log backup alındığında temizlenir. Log backup hiç alınmıyorsa log, disk dolana kadar büyür. Çözüm, düzenli log backup’tır. Microsoft, çoğu sistem için 15–30 dakikada bir alınan log backup’ın yeterli olabileceğini belirtir. Daha sık backup hem veri kaybı riskini hem de log boyutunu azaltır.

BACKUP LOG [SalesDB]
TO DISK = N'E:\Backup\SalesDB_LOG_20261006_1100.trn'
WITH COMPRESSION, CHECKSUM;

Log daha önce hiç yedeklenmediyse, Microsoft’a göre log’un temizlenebilmesi için iki log backup almanız gerekebilir. Bu backup’ları bir SQL Server Agent job’ı ile zamanlayın ve job’ın gerçekten çalıştığını izleyin.

Veritabanının belirli bir ana geri dönme ihtiyacı yoksa (örneğin bir staging veritabanı), doğru çözüm log backup değil, simple recovery model olabilir. Bu kararı nasıl vereceğinizi SQL Server Simple Recovery Model yazımızda anlattık. Ancak production’daki bir veritabanını yalnızca log’u küçültmek için simple’a almak, log zincirini kırar ve point-in-time restore yeteneğini sessizce ortadan kaldırır.

ACTIVE_TRANSACTION: açık kalmış bir transaction

Önce en eski açık transaction’ı bulun:

-- En eski aktif transaction
DBCC OPENTRAN (N'SalesDB');

-- Veritabanındaki açık transaction'lar, oturum bilgisi ve kullandıkları log
SELECT s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       dt.database_transaction_begin_time,
       DATEDIFF(MINUTE, dt.database_transaction_begin_time, SYSDATETIME()) AS open_minutes,
       dt.database_transaction_log_bytes_used / 1048576 AS log_used_mb
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st ON dt.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions AS s ON st.session_id = s.session_id
WHERE dt.database_id = DB_ID(N'SalesDB')
ORDER BY dt.database_transaction_begin_time;

Kalıcı çözüm genellikle uygulama tarafındadır: büyük güncellemeleri ve silmeleri küçük batch’lere bölmek, transaction’ları gereğinden uzun açık tutmamak. SQL Server 2019 ve sonrasındaki Accelerated Database Recovery (ADR) özelliği de uzun transaction’ların recovery ve rollback süresi üzerindeki etkisini önemli ölçüde azaltır.

AVAILABILITY_REPLICA ve REPLICATION

Bu durumlarda log, ona bağımlı bir özellik veriyi tüketemediği için tutuluyor. Always On’da secondary replikaların bağlantı ve senkronizasyon durumunu, replication’da ise Log Reader Agent’ı ve dağıtım veritabanını kontrol edin. Askıya alınmış (suspended) bir AG veritabanı ya da durmuş bir replication agent’ı, log’un günlerce büyümesine neden olabilir. Always On mimarisi için SQL Server Always On rehberimize göz atabilirsiniz.

Acil durum: log diski doldu (Error 9002)

Log dolduğunda SQL Server 9002 hatası verir ve veritabanı yazma işlemlerini kabul etmez. Önce yukarıdaki teşhisi yapın. Disk dolduğu için log büyüyemiyorsa, Microsoft’un önerdiği güvenli seçenekler şunlardır:

• Aynı diskte başka dosyaları taşıyarak veya silerek yer açmak.
• Log dosyasını daha büyük bir diske taşımak.
• Başka bir diske geçici ikinci bir log dosyası eklemek (ALTER DATABASE ... ADD LOG FILE). Asıl neden çözüldükten sonra bu dosya kaldırılmalıdır.

Adım 3: Log’u Güvenli Şekilde Küçültmek

Asıl neden çözüldükten ve log temizlendikten sonra, dosya gereğinden büyük kaldıysa tek seferlik ve kontrollü bir shrink yapılabilir:

USE [SalesDB];

-- 1) Full modda: önce log backup alarak log'un temizlendiğinden emin olun
BACKUP LOG [SalesDB]
TO DISK = N'E:\Backup\SalesDB_LOG_before_shrink.trn'
WITH COMPRESSION, CHECKSUM;

-- 2) Log dosyasının logical adını ve mevcut boyutunu bulun
SELECT name, size / 128 AS size_mb, growth, is_percent_growth
FROM sys.database_files
WHERE type = 1;

-- 3) Log'u hedef boyuta küçültün (MB)
DBCC SHRINKFILE (N'SalesDB_log', 1024);

-- 4) Gerçek ihtiyaca göre tek adımda büyütün ve sabit bir autogrowth verin
ALTER DATABASE [SalesDB]
MODIFY FILE (NAME = N'SalesDB_log', SIZE = 8192MB, FILEGROWTH = 512MB);

Bilinmesi gerekenler:

• Shrink yalnızca log dosyasının sonundaki boş bölümleri kaldırabilir. Dosyanın sonundaki bölüm hâlâ kullanılıyorsa shrink hedef boyuta inmez. Bir log backup daha alıp tekrar denemek genellikle yeterlidir.
• Log’u, normal iş yükünde tekrar ulaşacağı boyutun altına küçültmeyin. Microsoft, düzenli iş yükü nedeniyle büyüyen dosyaların shrink edilmesini önermez: log tekrar büyüdüğünde, büyüme süresince işlemler yavaşlar.
• Shrink’i bakım planına veya düzenli bir job’a koymayın; AUTO_SHRINK seçeneğini de açmayın. Microsoft, shrink’in rutin bir bakım işlemi olarak görülmemesi gerektiğini açıkça belirtir. Ayrıntılar için SQL Server Shrink İçin Best Practices yazımıza bakabilirsiniz.

VLF’ler: Log’un Görünmeyen Performans Sorunu

SQL Server her log dosyasını içeride virtual log file (VLF) adı verilen parçalara böler. Her autogrowth olayında yeni VLF’ler oluşturulur. Microsoft’un belgelediği kurala göre:

• Büyüme, mevcut log boyutunun 1/8’inden küçükse (SQL Server 2014 ve sonrası) tek bir VLF oluşturulur.
• Aksi halde: 64 MB’tan küçük büyümelerde 4 VLF, 64 MB ile 1 GB arasında 8 VLF, 1 GB’tan büyük büyümelerde 16 VLF oluşturulur. SQL Server 2022 ve sonrasında 64 MB’a kadar olan büyümeler tek bir VLF oluşturur.

Log, yıllar boyunca küçük adımlarla (örneğin eski varsayılan olan %10 veya 1 MB) büyüdüyse on binlerce VLF oluşabilir. Microsoft’a göre çok sayıda VLF; veritabanı açılışını, log backup ve restore işlemlerini yavaşlatır ve replication, CDC ve Always On redo gecikmelerine yol açar. VLF sayısını kontrol etmek için:

SELECT d.name,
       COUNT(*) AS vlf_count,
       SUM(CASE WHEN li.vlf_active = 1 THEN 1 ELSE 0 END) AS active_vlfs
FROM sys.databases AS d
CROSS APPLY sys.dm_db_log_info(d.database_id) AS li
WHERE d.state_desc = N'ONLINE'
GROUP BY d.name
ORDER BY vlf_count DESC;

Microsoft, toplam VLF sayısını birkaç bin ile sınırlı tutmayı önerir. Çok yüksek bir sayı görüyorsanız, düzeltme yöntemi yukarıdaki shrink adımlarıyla aynıdır: log’u küçültüp ihtiyaç duyulan boyuta az sayıda büyük adımla yeniden büyütmek. Konunun ayrıntıları için Yüksek VLF Sayıları SQL Performansınızı Nasıl Öldürür? yazımıza bakabilirsiniz.

Doğru Log Boyutu ve Autogrowth Ayarları

Sorunu kalıcı olarak önlemenin yolu, log’u baştan doğru boyutlandırmaktır. Microsoft’un önerileri şöyle özetlenebilir:

• Log’u en yoğun dönemine göre boyutlandırın. Log kullanımını şu işlemler sırasında izleyin: full backup süresince (bu sırada log backup’lar log’u temizleyemez), en büyük index bakımı sırasında ve veritabanındaki en büyük batch işlemi sırasında.
• Autogrowth’u yüzde değil, sabit MB olarak ayarlayın. Yüzde ile büyüme, log büyüdükçe giderek büyüyen ve öngörülemeyen adımlar oluşturur.
• Log için FILEGROWTH değerini 1.024 MB’ın üzerine çıkarmayın. SQL Server 2016’dan itibaren yeni veritabanlarında varsayılan değer 64 MB’tır. SQL Server 2022 ve sonrasında 64 MB’a kadar olan log büyümeleri instant file initialization’dan yararlanır ve neredeyse anında tamamlanır.
• Birden fazla log dosyası performansı artırmaz. Log dosyaları sırayla kullanılır; ikinci bir log dosyası yalnızca geçici bir alan çözümü olabilir.
• Log’u hızlı ve ayrı bir diske koyun. Log yazmaları sıralıdır ve her commit bunları bekler; log diskinin gecikmesi doğrudan işlem süresine yansır. Bkz. SQL Server’da Disk Depolama İçin Best Practices.

Log Büyümesini Önceden Yakalamak

Log’un dolduğunu kullanıcılar şikâyet ettiğinde öğrenmek yerine, bir eşik aşıldığında uyarı almak çok daha iyidir. SQL Server Agent’ın performans koşulu alert’leri ile log doluluk oranını izleyebilirsiniz:

-- SalesDB log'u %80'i geçtiğinde uyar
-- (Named instance'ta nesne adı 'MSSQL$InstanceAdi:Databases' şeklindedir)
EXEC msdb.dbo.sp_add_alert
     @name = N'SalesDB - Percent Log Used > 80',
     @performance_condition = N'SQLServer:Databases|Percent Log Used|SalesDB|>|80',
     @delay_between_responses = 900;

EXEC msdb.dbo.sp_add_notification
     @alert_name = N'SalesDB - Percent Log Used > 80',
     @operator_name = N'DBA Team',
     @notification_method = 1;

-- Log tamamen dolduğunda (Error 9002) uyar
EXEC msdb.dbo.sp_add_alert
     @name = N'Error 9002 - Transaction Log Full',
     @message_id = 9002,
     @severity = 0,
     @delay_between_responses = 300;

Alert altyapısının nasıl doğru kurulacağını SQL Server Alert’leri: Hangileri Açılmalı ve Doğru Nasıl Kurulur? yazımızda ayrıntılı olarak anlattık. 9002 hataları ayrıca SQL Server error log‘a da yazılır.

Sık Sorulan Sorular