MSSQL Server DBCC LOGINFO Verilerini Okumak

SQL Server’da Transaction Log dosyası (LDF), VLF (Virtual Log File) adı verilen küçük parçalara bölünür. VLF’in kendi içindeki doluluk oranını (yüzde bazında veya MB bazında) standart komutlarla göremezsin.

SQL Server mimarisinde bir VLF ya Active (içinde veri var/kullanılıyor) ya da Reusable (boş/üzerine yazılabilir) durumdadır. VLF’in “yarısı dolu, yarısı boş” gibi bir durumu SQL Server motoru için anlamlı değildir; VLF tamamen bir bütün olarak işaretlenir.

Transaction Log, “Write-Ahead Logging” prensibiyle çalışır. Veriler sıralı (sequential) yazılır.

SQL Server, mevcut VLF dolduğunda bir sonrakine geçer.
Bir VLF’in içindeki verinin bir kısmı silinse bile, o VLF “Active” kalmaya devam eder.
Ancak bir Checkpoint ve Log Backup (Full Recovery modeldeyseniz) işlemi gerçekleştikten sonra, içindeki tüm kayıtlar “gereksiz” hale gelirse VLF’in tamamı Status 0 (boş) olur.

Mevcut durumu analiz etmek için şu yöntemleri kullanabilirsin:

1. DBCC LOGINFO ile VLF Durumu

Bu komut, her bir VLF’in durumunu gösterir. Buradaki en kritik sütun Status sütunudur.

Status 0: VLF boştur (Reusable).
Status 2: VLF doludur (Active).

USE DB_NAME
DBCC LOGINFO;

İkinci komut ise:

DBCC LOGINFO(DB_NAME)

Parity, SQL Server’ın kendi kendine tuttuğu bir “not defteri işareti” gibidir. Kurtarma (Recovery) anında SQL Server bu sayılara bakarak: “Tamam, 128’ler yeni yazılanlar, 64’ler ise bir önceki turdan kalan eski veriler” ayrımını yapar.

2. sys.dm_db_log_info (Modern Yöntem)

SQL Server 2016 SP2 ve sonrasını kullanıyorsan, bu DMV çok daha detaylı bilgi verir. Buradaki vlf_status sütunu yine 0 veya 2 değerini alır.

SELECT 
    vlf_begin_offset,
    vlf_sequence_number,
    vlf_size_mb,
    vlf_status, -- 2 ise aktif, 0 ise boş
    vlf_sequence_number,
    vlf_parity
FROM sys.dm_db_log_info(DB_ID('DB_NAME'));

Eğer 1000 tane VLF’in varsa, bu bir “VLF Fragmentation” sorunudur. Bu durum performansı (özellikle veritabanı açılışı ve restore sürelerini) olumsuz etkiler.

İdeal olan:

VLF sayısını makul seviyelerde (genelde 50-100 arası) tutmaktır.
Bunun için Log dosyasını SHRINK yapıp, uygun büyüklükteki GROWTH adımlarıyla tekrar büyütmen önerilir.

Şu anki log dosyanın toplam doluluk oranını merak ediyorsan şu komutu kullanabilirsin:

DBCC SQLPERF(LOGSPACE);

Vlf durumunu detaylı görmek için aşağıdaki komutun kullanıldığını söylemiştik. Şimdi ilgili komut sonucunda nelerin yapılması gerektiğini ele alalım.

DBCC LOGINFO(DB_NAME)

Tablodaki en kritik nokta 6. satırdır.

Status = 2: Bu VLF şu an aktif. Yani içinde ya henüz yedeklenmemiş bir işlem var ya da hala açık olan (commit edilmemiş) bir transaction var demektir.
FSeqNo = 247: Bu, SQL Server’ın o an yazdığı en güncel seri numarasıdır.

Shrink işlemi dosyanın sonundan (12. satırdan) başa doğru gelir.

12’den 7’ye kadar olan kısımda Status değeri 0 (boş). Shrink komutu verdiğinde SQL Server bu parçaları dosyadan kesip atabilir.
6. Satıra Geldiğinde Shrink işlemi durur. Çünkü 6. satır Status = 2 (aktif). Bu VLF dosyanın ortasında bir duvar gibi durduğu sürece, üzerindeki (1-5 arası) boş VLF’leri de silemezsin.

Çünkü bir dosyayı ortasından veya başından kesip kısaltamazsın. Dosya ancak sonundan kırpılabilir. 6. satır orada “aktif” durduğu sürece, kendisinden önceki boş VLF’leri (1-5) koruma kalkanı altına alır. Dosya boyutu fiziksel olarak 6. satırın bittiği yere kadar takılı kalır.

Bu dosyayı tamamen küçültmek istiyorsan, o Status 2 olan kısmı dosyanın en başına (1. satıra) “kovalaman” gerekir.

SQL Server log’u dairesel (circular) yazar, Eğer dairesel olmasaydı, veritabanı her işlem yaptığında log dosyası sonsuza kadar uzamak zorunda kalırdı. Dairesel yapı sayesinde SQL Server, işi biten (yedeklenen) eski alanları tekrar tekrar kullanır. Dosya boyutun sabit kalabilir.

“Dairesel yazım” (circular) mantığını bir koşu pistine benzetirsin. SQL Server log dosyasını (LDF) sonu olmayan bir şerit gibi değil, kendi kuyruğunu kovalayan bir çember gibi kullanır.

Log dosyan 12 adet VLF’den (Virtual Log File) oluşan dairesel bir koşu pisti olsun.

Pistteki her bir bölüm bir VLF’dir.
Status 2 (Aktif): Koşucunun (SQL Server’ın veriyi yazdığı yer) şu an bulunduğu ve bastığı yerdir.
Status 0 (Boş): Koşucunun henüz gelmediği veya üzerinden geçip gittiği, artık temizlenmiş alanlardır.

Sen veritabanında işlem yaptıkça SQL Server VLF’leri sırayla doldurur:

VLF 1’e yazar, dolunca VLF 2’ye geçer.
Böylece 12. VLF’e kadar gelir.
Dönüş Anı: 12. VLF dolduğunda SQL Server durmaz. Eğer 1. VLF yedeklenmiş veya boşaltılmışsa (Status 0 ise), tekrar 1. VLF’in başına döner ve üzerine yazmaya başlar.

Yukarıdaki resimde FSeqNo (Sıra Numarası) 12. satırda 242 iken, 1. satırda 241‘den başlayıp 6. satırda 247‘ye kadar yükselmiş. Koşucu pistin sonuna (12) gelmiş, sonra başa (1) dönüp 6. bölüme kadar tekrar koşmuş.

FSeqNo 236-240 (Satır 7-11): Bunlar bir önceki turdan kalma eski veriler. Şu an boştalar (Status 0).
FSeqNo 241-247 (Satır 1-6): SQL Server turu tamamlayıp dosyanın başına dönmüş ve tekrar yazmaya başlamış.
Sıradaki Yazma: SQL Server şu an 6. satırı bitirdikten sonra (eğer 7. satır yedeklenip boşaltılmışsa) 7. satıra FSeqNo 248 olarak yazmaya devam edecek.

Shrink işlemi “fiziksel” bir temizlikçidir. Dosyanın sonundan başlar. Yukarıdaki resminden örnek verelim:

Fiziksel Son (12. VLF): Shrink gelir, 12 numaralı parçayı koparmak ister. Bakar ki Status 0, koparır.
Fiziksel Orta (6. VLF): Geriye doğru gelerek 6. parçaya ulaşır. Bakar ki Status 2 (Yani koşucu tam orada duruyor!).
Engel: Burada aktif veri var, bu parçayı koparırsam zincir bozulur der ve DURUR.

Sorun burada başlar 6. satır “dolu” olduğu sürece, ondan daha önce gelen (1, 2, 3, 4, 5) VLF’ler Status 0 (boş) olsa bile shrink onlara ulaşamaz. Çünkü onlar dosyanın “başında” veya “ortasında” kalmıştır.

Özetle: Bir kağıdı rulo yapıp ortasından bir parça kesip çıkaramazsın; sadece ucundan kısaltabilirsin. 6. VLF senin “yolunu kapatan” son noktadır.

En Eski Kayıt (Min LSN): Muhtemelen 6. satırın içindedir (çünkü Status 2 olan tek yer orası).

En Yeni Kayıt: Yine 6. satırdır, çünkü SQL Server şu an oraya yazıyor.

Boş Alanlar: 7’den 12’ye kadar olan kısım ve 1’den 5’e kadar olan kısım şu an boş (Status 0). Ancak 1-5 arası, 6. satır “önlerini kapattığı” için fiziksel olarak dosyadan atılamaz (Shrink edilemez).

Eğer bu dosyayı küçültmek (Shrink) istiyorsan, sadece komut çalıştırmak yetmez. Şu sırayı izlemelisin:

Log Yedeklemesi Al (veya Checkpoint): Bu işlem 6. satırın statüsünü 2’den 0’a düşürmeye çalışır.
Dosyanın “Başa Dönmesini” Bekle: Bazen aktif VLF dosyanın ortasında kalır. Bir miktar “dummy” (sahte) işlem yaparak veya logu kaydırarak aktif kısmın dosyanın en başına (1. satıra) gelmesini sağlarsan, arkadaki (7-12 arası) devasa boşluğu tek seferde SHRINK ile atabilirsin.

Özet: Resmindeki 6. satır şu an senin “engelcin”. O 2 olduğu sürece dosyanın fiziksel boyutunu radikal bir şekilde küçültemezsin.

İçeride açık bir transaction (Open Tran) vardır. Onu bulup kapatmalısın (DBCC OPENTRAN). Bu komutun görevi sadece “tespit etmek”tir. SQL Server üzerinde o an açık kalmış, tamamlanmamış en eski aktif işlemi (transaction) size gösterir. Bir nevi “Kim bu içeride kapıyı açık unutan?” diye bakmanızı sağlayan bir fenerdir.

Önce veritabanına geçiş yapıp sonra komutu çalıştırabilirsiniz:

USE VeritabaniAdiniz;
GO
DBCC OPENTRAN;
GO

SQL Server’da bir satırı sildiğinizde, bu satır fiziksel olarak hemen diskten kaldırılmaz; sadece “silinmiş” (ghost) olarak işaretlenir. Ghost Cleanup Task, arka planda çalışan ve bu işaretlenmiş satırları fiziksel olarak temizleyerek boş alan açan bir sistem sürecidir. GhostCleanupTask işlemi shrink (dosya küçültme) işlemini doğrudan engelleyebilir veya yavaşlatabilir.

Veritabanı değiştirmeden, doğrudan veritabanı adını parantez içinde yazarak da sorgulayabilirsiniz:

DBCC OPENTRAN ('VeritabaniAdiniz');

Eğer sunucudaki tüm veritabanlarında açık işlem olup olmadığını hızlıca taramak isterseniz, şu pratik script’i kullanabilirsiniz:

EXEC sp_MSforeachdb 'SELECT ''?'' AS DatabaseName; USE [?]; DBCC OPENTRAN';

SQL Server aslında bize neden logu temizleyemediğini açıkça söyler. Önce bu komutu çalıştır:

SELECT name, log_reuse_wait_desc 
FROM sys.databases 
WHERE name = 'Veritabani_Adiniz';

Bu sorgu sonucunda şunlardan birini göreceksin:

LOG_BACKUP: “Bana bir Log Backup al, yoksa bu VLF’i boşaltmam” diyor.
ACTIVE_TRANSACTION: “İçeride bitmemiş bir iş var, o bitmeden burayı silemem” diyor.
REPLICATION / CHANGE_TRACKING: Veriler başka bir yere aktarılmayı bekliyor.
CHECKPOINT: Veriler henüz diskteki veri dosyasına (MDF) yazılmamış.

Eğer üstteki sorguda ACTIVE_TRANSACTION cevabını aldıysan, o VLF’i rehin tutan açık işlemi şu komutla bulabilirsin:

DBCC OPENTRAN('Veritabani_Adiniz');

Bu komut sana şunları verir:

Transaction ID: İşlemin kimliği.
SPID: Bu işlemi yapan kişinin/uygulamanın bağlantı numarası.
Start Time: Bu işlem ne zaman başladı? (Bazen 3 gün önceki bir işlem unutulmuş olabilir ve logun boşalmasını engelliyor olabilir).

303250 (VLF Sequence Number): Sanal Günlük Dosyası (Virtual Log File) numarasını gösterir. Log dosyasının hangi bölümünde işlem yapıldığını belirtir.
264118 (Log Block Offset): Bu işlem kaydının, ilgili VLF içindeki başlangıç noktasını (ofsetini) gösterir.
2 (Slot Number): Belirli bir log bloğu içindeki işlemin sıra numarasıdır.

“Ben bizzat o VLF’in içinde ne yazıyor, hangi tablo güncelleniyor görmek istiyorum” dersen, SQL Server’ın gizli fonksiyonunu kullanabilirsin.

Eğer DBCC OPENTRAN ile bir SPID bulduysan ve bu işlemin artık gereksiz olduğuna eminsen:

KILL <SPID> komutuyla işlemi durdurabilirsin.
İşlem durunca (Rollback bitince) VLF Status 0 olur.
Ardından SHRINK işlemin o noktayı aşarak devam edebilir.

Eğer log_reuse_wait_desc kısmında AVAILABILITY_REPLICA görüyorsan, bu durum Transaction Log’daki o Status 2 olan VLF’in neden “rehin” tutulduğunu açıkça ortaya koyuyor. Always On Availability Groups (AG) yapısı kullanıyorsun ve Primary sunucun, üzerindeki log kayıtlarını Secondary sunucuya göndermiş ancak karşı taraftan “Tamam, ben bu kayıtları aldım ve güvenli bir şekilde kendi loguma yazdım” teyidini (acknowledgment) henüz almamış.

O VLF’in içindeki işlemler, diğer sunucuya başarıyla işlenene kadar SQL Server bu VLF’i asla boşaltmaz (Status 0 yapmaz). Haliyle Shrink işlemi de o noktada takılı kalır.

Secondary sunucu şu nedenlerle bu onay işlemini geciktiriyor olabilir:

Network Gecikmesi (Latency): İki sunucu arasındaki hat yavaştır.
REDO Thread Takılması: Secondary sunucu aldığı kayıtları kendi veritabanına işlerken bir darboğaz yaşıyordur.
Replica Durumu (Disconnected/Suspended): Secondary sunucu ile bağlantı kopmuş olabilir veya senkronizasyon duraklatılmış olabilir.

O VLF’in içerisinde hangi replikanın, ne kadar veriyi beklettiğini şu DMV (Dynamic Management View) sorgusuyla görebilirsin:

SELECT 
    ar.endpoint_url, 
    r.last_redone_time,
    r.log_send_queue_size, -- KB cinsinden gönderilmeyi bekleyen log
    r.redo_queue_size      -- KB cinsinden işlenmeyi bekleyen log
FROM sys.dm_hadr_database_replica_states r
JOIN sys.availability_replicas ar ON r.replica_id = ar.replica_id
WHERE r.database_id = DB_ID('Senin_DB_Adin');

Eğer log_send_queue_size veya redo_queue_size değerleri yüksekse, o VLF’i kilitleyen şey bu kuyruğun kendisidir.

O VLF’in Status 0 olması ve Shrink’in çalışabilmesi için şu yolları izleyebilirsin:

Secondary sunucunun ayakta ve senkronize (SYNCHRONIZED veya SYNCHRONIZING) olduğundan emin ol.
Eğer “Suspended” durumundaysa, veritabanına sağ tıklayıp “Resume Data Movement” de.
Eğer Secondary sunucuya bir daha ulaşılamayacaksa ve log dosyan şişip diski bitiriyorsa, o database’i Availability Group’tan geçici olarak çıkarman gerekebilir. Bu durumda loglar artık replikasyon beklemez ve ilk log backup ile VLF’ler boşa çıkar.

Async modda bile olsa, Primary sunucu her log kaydını Secondary’ye göndermek zorundadır. Fark sadece şudur: Sync modda “yazdım, karşıdan onay gelene kadar işlemi bitirmiyorum” der, Async modda ise “yazdım, karşıya gönderilmesi için sıraya koydum, işime devam ediyorum” der. Async mod performansı artırır ancak log yönetimini muaf kılmaz. SQL Server, verinin güvenliği için Async replikada dahi ‘gönderilmemiş’ her log kaydını VLF içinde mühürlü tutar; bu da log dosyasının küçültülmesine (Shrink) engel olan en büyük ‘görünmez’ duvardır.

Özetle: Status 2 olan VLF’in şu an pasaport kontrolünde bekleyen bir yolcu gibi. Karşı sunucu (Replica) onay vermeden SQL Server o yolcuyu pistten (VLF) çıkarmıyor.

Async modda AVAILABILITY_REPLICA beklemesinin 3 ana sebebi vardır:

Log Send Queue (Gönderim Kuyruğu): Ana sunucu logu üretiyor ama network üzerinden karşıya gönderemiyor. Karşı sunucu (Secondary) yavaş olabilir veya network darboğazı vardır.
Disconnected State: Eğer Secondary sunucu ile bağlantı koptuysa, Primary sunucu “Belki birazdan gelir” diyerek o logları saklamaya başlar. Bağlantı kurulana kadar log dosyan şişer ve VLF’ler kilitli kalır.
Secondary Veritabanının “Suspended” Olması: Eğer Secondary tarafında bir hata oluştuysa ve veri akışı durduysa (Pause/Suspend), Primary sunucu logları temizleyemez.

Async modda olsan dahi, Shrink işleminin o Status 2 olan VLF’i geçebilmesi için şu sırayı takip etmelisin:

sys.dm_hadr_database_replica_states üzerinden log_send_queue_size değerinin 0 olduğundan emin ol.
Eğer veritabanın FULL recovery moddaysa (ki AG için zorunludur), logun “truncate” edilmesi için mutlaka bir Transaction Log Backup almalısın. Backup alınca, karşıya gitmiş olan kayıtlar VLF’i boşa çıkarır (Status 0).
sys.dm_hadr_database_replica_states sorgusunda synchronization_state_desc kolonuna bak. Eğer NOT SYNCHRONIZING yazıyorsa, Async mod bile olsa logları rehin tutar.

Yukarıdaki tüm işlemler için bir çözüm yolu üretilemiyorsa Replica sunucunun Restart olunması sorunu çözecektir. Sql servis hesabının Restart verilmesi yeterli değildir.

Başka makalede görüşmek dileğiyle…

Anne babaya kaba davranmayın İsra-23

Bir yanıt yazın