Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

Microsoft SQL Server yöneten Veritabanı Yöneticileri (DBA’lar) ve DevOps mühendisleri için, Hata 9002: ‘X’ veritabanı için işlem günlüğü (transaction log) dolu uyarısı kadar anında endişe yaratan çok az uyarı vardır. İşlem günlüğü dolduğunda ve genişleyemediğinde, veritabanı fiilen salt okunur hale gelir. Tüm INSERT, UPDATE ve DELETE işlemleri durur, uygulama işlemleri başarısız olur ve üretim süreci durma noktasına gelir.

SQL Server işlem günlüğü mimarisini anlamak, kök nedeni doğru bir şekilde teşhis etmek ve hızlı kurtarma prosedürlerini uygulamak, yüksek erişilebilirliği sürdürmek için kritik becerilerdir. Bu kapsamlı kılavuz, işlem günlüğünün mekaniğini, acil bir durumda dolu bir günlüğün nasıl çözüleceğini ve bunun tekrar yaşanmasını önlemek için mimari en iyi uygulamaları incelemektedir.

SQL Server İşlem Günlüğü Mimarisi

Dolu bir işlem günlüğünde sorun gidermek için, önce SQL Server’ın verileri nasıl yazdığını ve yönettiğini anlamanız gerekir.

Önceden Yazma Günlüğü (Write-Ahead Logging – WAL)

SQL Server, Önceden Yazma Günlüğü (WAL) protokolünü kullanır. Bir veri değişikliği gerçekleştiğinde, değişiklik önce bellekteki işlem günlüğüne yazılır, ardından veritabanı dosyalarındaki (MDF/NDF) gerçek veri sayfaları güncellenmeden önce diskteki fiziksel günlük dosyasına aktarılır. Bu, ACID (Atomiklik, Tutarlılık, İzolasyon, Dayanıklılık) uyumluluğunu garanti eder ve bir çökme durumunda SQL Server’ın işlemleri yeniden oynatabilmesini (ileri sarma) veya geri alabilmesini (geri alma) sağlar.

Sanal Günlük Dosyaları (VLF) ve Döngüsel Günlükleme

Dahili olarak, fiziksel işlem günlüğü dosyası (LDF), Sanal Günlük Dosyaları (VLF) adı verilen daha küçük, mantıksal bölümlere ayrılmıştır. İşlem günlüğü döngüsel olarak çalışır. Günlük kayıtları yazıldıkça, bir VLF’yi doldurur ve bir sonrakine geçer.

Günlük, fiziksel dosyanın sonuna ulaştığında, başa dönmeye çalışır. Ancak, bir VLF’nin üzerine yalnızca o VLF etkin değil (inactive) olarak işaretlenmişse yazabilir. Tüm VLF’ler etkinse (yani SQL Server tarafından hala ihtiyaç duyulan günlük kayıtlarını içeriyorlarsa), günlük başa dönemez. Otomatik büyüme (auto-growth) etkinse ve disk alanı mevcutsa, fiziksel dosya büyür. Disk doluysa veya otomatik büyüme kısıtlanmışsa, Hata 9002 ile karşılaşırsınız.

Günlük Kırpma (Truncation) vs. Günlük Küçültme (Shrinking)

Yaygın bir yanlış kanı, günlüğü kırpmanın fiziksel dosya boyutunu küçülttüğüdür.
* Günlük Kırpma (Log Truncation): Etkin VLF’leri etkin değil olarak işaretleme ve alanı yeniden kullanım için uygun hale getirme işlemidir. Disk üzerindeki LDF dosyasının boyutunu küçültmez.
* Günlük Küçültme (Log Shrinking): LDF dosya boyutunu fiziksel olarak azaltma ve alanı işletim sistemine geri verme işlemidir.

Tam (Full) Kurtarma modelinde, günlük kırpma yalnızca bir işlem günlüğü yedeği başarıyla tamamlandığında gerçekleşir (günlüğü etkin tutan başka bir işlem olmadığını varsayarsak).

“İşlem Günlüğü Dolu” Hatasını (Hata 9002) Teşhis Etme

Günlük dolduğunda, ilk adımınız körü körüne disk alanı eklemek veya dosyaları küçültmek olmamalıdır. Günlüğün neden kırpılamadığını belirlemelisiniz. SQL Server, sys.databases katalog görünümü aracılığıyla günlüğün yeniden kullanılmasını neyin engellediğini size tam olarak söyleyen yerleşik bir mekanizma sağlar.

Darboğazı belirlemek için aşağıdaki T-SQL komutunu çalıştırın:

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

Ayrıca işlem günlüklerinizin mevcut alan kullanımını şu şekilde kontrol edebilirsiniz:

DBCC SQLPERF(LOGSPACE);

Yaygın log_reuse_wait_desc Durumları

  1. LOG_BACKUP: Veritabanı Tam veya Toplu Günlüklü (Bulk-Logged) kurtarma modelindedir ve yakın zamanda bir işlem günlüğü yedeği alınmamıştır. Bu en yaygın nedendir.
  2. ACTIVE_TRANSACTION: Uzun süren bir işlem (örneğin, büyük bir dizin yeniden oluşturma veya unutulmuş, tamamlanmamış bir işlem) günlüğü etkin tutuyordur.
  3. REPLICATION / CDC: İşlemsel Çoğaltma (Transactional Replication) veya Değişiklik Verisi Yakalama (CDC) etkindir ve Günlük Okuyucu Aracısı (Log Reader Agent) işlemleri henüz işlememiştir.
  4. AVAILABILITY_REPLICA: AlwaysOn Kullanılabilirlik Grubunda, ikincil bir kopya bağlantısı kesilmiş veya çok yavaş senkronize oluyor, bu da birincil kopyayı günlük kayıtlarını ikincil kopya üzerinde sertleşene kadar tutmaya zorluyor.

Hızlı Kurtarma Stratejileri: Sorunu Üretimde Çözme

Döndürülen log_reuse_wait_desc değerine bağlı olarak, acil durum yanıtınız değişecektir. En yaygın senaryolar için hızlı kurtarma stratejileri şunlardır.

Senaryo 1: Eksik veya Başarısız Günlük Yedekleri (LOG_BACKUP)

Bekleme türü LOG_BACKUP ise, çözüm basittir: işlem günlüğünü yedeklemeniz gerekir.

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

Yedekleme tamamlandığında, etkin olmayan VLF’ler kırpılacak ve SQL Server normal işlemlerine devam edecektir. Yedekleme sürücünüz doluysa, geçici bir ağ paylaşımına veya bir null cihazına yedeklemeniz gerekebilir (veritabanı kolayca yeniden oluşturulabilir değilse, günlük zincirini kırdığı için şiddetle tavsiye edilmez):

-- UYARI: Bu, günlük zincirini kırar ve noktadan noktaya kurtarmayı tehlikeye atar.
-- Sadece kesinlikle gerekliyse kullanın ve hemen ardından TAM yedekleme yapın.
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

Senaryo 2: Uzun Süren Etkin İşlemler (ACTIVE_TRANSACTION)

Tek bir işlem saatlerce çalışıyorsa, tüm süre boyunca günlük kırpılmasını engeller. İlk olarak, sorunlu işlemi tanımlayın:

DBCC OPENTRAN('YourDatabaseName');

Bu komut, en eski etkin işlemi ve onun Sunucu İşlem Kimliğini (SPID) döndürür. Dinamik yönetim görünümlerini (DMV’ler) sorgulayarak SPID’nin ne yaptığı hakkında daha fazla ayrıntı toplayabilirsiniz:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

İşlem hatalı bir sorgu veya durmuş bir süreçse, günlüğü serbest bırakmak için onu sonlandırmanız gerekebilir.

KILL <SPID>;

Not: Büyük bir işlemi sonlandırmak, önemli miktarda zaman alabilen ve geçici olarak ek günlük etkinliği oluşturacak bir geri alma işlemini tetikleyecektir. Geri alma sırasında SQL Server hizmetini yeniden başlatmayın, aksi takdirde veritabanı yeniden başlatıldığında kurtarma moduna girer.

Senaryo 3: Acil Durum Alan Tahsisi (Disk %100 Dolu)

LDF dosyası tüm sürücüyü tükettiyse, yedekleme bile yapamazsınız çünkü SQL Server yedekleme olayını kaydetmek için az miktarda günlük alanına ihtiyaç duyar. Bu senaryoda, kullanılabilir alanı olan farklı bir sürücüde ikincil bir günlük dosyası eklemelisiniz.

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

Bu, SQL Server’a anında nefes alacak alan sağlar. Veritabanı çevrimiçi olduğunda, bir işlem günlüğü yedeği alın, ikincil günlük dosyasını boşaltın ve kaldırın:

-- 1. Günlüğü kırpmak için bir günlük yedeği alın
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. Geçici günlük dosyasını boşaltın
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. Geçici günlük dosyasını kaldırın
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

İşlem Günlüğü Önleme ve Yönetimi İçin En İyi Uygulamalar

Reaktif sorun giderme streslidir ve SLA’ları etkiler. Kurumsal veritabanı kararlılığı için proaktif mimari ve operasyonel en iyi uygulamaları uygulamak esastır.

1. Sağlam, Otomatik Bir Yedekleme Stratejisi Uygulayın

Bir veritabanı Tam kurtarma modelindeyse, sık işlem günlüğü yedeklemeleri zorunludur. Kurtarma Noktası Hedefinize (RPO) ve işlem hacminize bağlı olarak, günlük yedeklemeleri her 5 ila 15 dakikada bir gerçekleşmelidir.

CloudSave gibi kurumsal yedekleme çözümleri bu süreci önemli ölçüde basitleştirir. VDI (Sanal Cihaz Arayüzü) aracılığıyla doğrudan SQL Server ile entegre olan CloudSave, DBA’ların politika odaklı, yüksek frekanslı işlem günlüğü yedeklemeleri yapılandırmasına olanak tanır. Bu, günlüklerin sürekli olarak kırpılmasını, güvenli bir şekilde şifrelenmesini ve tesis dışı veya değiştirilemez bulut depolama alanında saklanmasını sağlayarak, karmaşık özel SQL Agent işlerine ihtiyaç duymadan LOG_BACKUP bekleme durumunu önler.

2. İşlem Günlüğünü Doğru Boyutlandırın ve VLF’leri Yönetin

İşlem günlüğü boyutunuzu yönetmek için otomatik büyümeye güvenmek tehlikeli bir anti-kalıptır. Otomatik büyüme işlemleri maliyetlidir ve disk sıfırla başlatılırken (Anında Dosya Başlatma etkinleştirilmedikçe, ki bu günlük dosyaları için geçerli değildir) işlem işlemeyi duraklatır.

Ayrıca, sık ve küçük otomatik büyümeler (örneğin, her seferinde %10 veya 50MB büyütmek) VLF parçalanmasına yol açar. Binlerce küçük VLF’ye sahip bir işlem günlüğü, veritabanı başlatma sürelerini, yedekleme performansını ve çoğaltma gecikmesini ciddi şekilde düşürür.

  • Günlüğü önceden boyutlandırın: En büyük bakım işlemlerinizi (dizin yeniden oluşturma gibi) analiz edin ve LDF dosyasını büyümeye gerek kalmadan bunları karşılayacak şekilde önceden boyutlandırın.
  • Sabit otomatik büyüme ayarlayın: VLF’lerin sağlıklı bir boyutta oluşturulmasını sağlamak için otomatik büyümeyi yüzdeden sabit bir boyuta (örneğin 1GB veya 5GB) değiştirin.

VLF sayınızı aşağıdaki sorguyu kullanarak kontrol edebilirsiniz (SQL Server 2017+ için):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

VLF sayınız 500’ün üzerindeyse, sessiz bir dönem beklemeyi, günlüğü minimum boyuta küçültmeyi ve manuel olarak büyük parçalar halinde gerekli boyutuna geri büyütmeyi düşünün.

3. Dizin Bakım İşlemlerini Optimize Edin

Dizin yeniden oluşturma işlemleri, Toplu Günlüklü kurtarma modelinde bile (dizin türüne bağlı olarak) tamamen günlüğe kaydedilen işlemlerdir. 500GB’lık bir dizini yeniden oluşturmak en az 500GB işlem günlüğü kaydı oluşturacaktır.

Bakım sırasında günlük şişmesini azaltmak için:
* Dizinleri yeniden oluştururken SORT_IN_TEMPDB = ON kullanın. Bu, sıralama aşamasını TempDB’ye aktararak kullanıcı veritabanının işlem günlüğü üzerindeki yükü azaltır.
* Mümkün olduğunda dizin yeniden oluşturma yerine dizin yeniden düzenleme (reorganize) kullanın, çünkü yeniden düzenlemeler günlük açısından daha verimlidir ve tüm işlemi geri almadan kesintiye uğratılabilir.
* Büyük DELETE veya UPDATE işlemlerini toplu (batch) hale getirin. 10 milyon satırı tek bir işlemde silmek yerine, 50.000’lik parçalar halinde silin, onaylayın ve günlük yedeklemelerinin gruplar arasında günlüğü kırpmasına izin verin.

4. Yüksek Erişilebilirlik ve Çoğaltma Topolojilerini İzleyin

AlwaysOn Kullanılabilirlik Gruplarında, birincil kopya, günlük kayıtları tüm senkron ve asenkron ikincil kopyalarda sertleşene kadar günlüğünü kırpamaz.

Bir ikincil kopya çevrimdışı olursa veya ağ bant genişliği birincil kopyanın işlem oluşturma hızına ayak uyduramazsa, birincil kopyanın gönderim kuyruğu büyüyecek ve günlük dolacaktır (AVAILABILITY_REPLICA bekleme türü).

SQLServer:Replica > Log Send Queue performans sayacı için sağlam bir izleme uygulayın. Bir ikincil kopya kalıcı olarak kaybolursa, onu Kullanılabilirlik Grubundan kaldırmanız veya birincil günlüğün kırpılmasına izin vermek için veri hareketini askıya almanız gerekir.

Sonuç

Dolu bir işlem günlüğü ile karşılaşmak veritabanı yöneticileri için bir geçiş törenidir, ancak uzun süreli kesintilerle sonuçlanmak zorunda değildir. Önceden Yazma Günlüğü ve VLF’lerin mekaniğini anlayarak, sys.databases kullanarak kök nedeni hızlı bir şekilde teşhis edebilir ve doğru hızlı kurtarma stratejisini uygulayabilirsiniz.

Uzun vadeli kararlılık, reaktif düzeltmelerden uzaklaşmaya bağlıdır. Günlük dosyalarınızı önceden boyutlandırmak, bakım rutinlerini optimize etmek ve sıkı, otomatik günlük yedekleme programlarını zorunlu kılmak için CloudSave gibi kurumsal sınıf yedekleme platformlarından yararlanmak, işlem günlüklerinizin sağlıklı, kırpılmış ve yüksek verimli üretim iş yüklerini desteklemeye hazır kalmasını sağlayacaktır.

Kategoriler