MSSQL Server Virtual Log file(VLF)

Bu makalede Virtual Log File konusunu ele almış olacağız. Virtual Log file adından da anlaşılacağı gibi sanal log dosyalarıdır. Bu sanal log dosyaları veritabanının ldf dosya uzantısının işletim sisteminizden  disk alanı tahsis etmesi sonucu oluşmaktadır.  Veritabanın işletim sisteminden ne kadar  disk alan tahsis edeceği ve  oluşan virtual log file sayısı veritabanına set edilen autogrowth değerine bağlıdır. Autogrowth değerinin ne kadar düşük olması vlf sayısının fazla olmasına sebep olacak.

Birden fazla vlf oluşması sistemin restart olurken geç ayağa kalkmasına  ve yoğun sistemlerde insert update delete işlemlerinde gecikmeye sebep olur. Herhangi bir restore işleminden sonra veritabanının geç ayağa kalkmasını sağlar. Çünkü içinde bulunan virtual log dosyalarını teker teker okur.

Aşırı VLF Sayısı = Performans Sorunu

• Çok fazla VLF olduğunda:
  • Backup/Restore yavaşlar
  • Database Recovery süresi uzar
  • Checkpoint, autogrow, log truncate gibi işlemler gecikir

Microsoft, VLF sayısının ideal olarak birkaç yüzü geçmemesi gerektiğini önerir.

VLF Yapısının Amaçları:

• Log işlemlerini daha yönetilebilir ve hızlı hale getirmek
• Checkpoint, recovery, transaction rollback, log shipping, replication gibi işlemlerin performansını optimize etmek

Not: Index rebuild işlemleri sırasında transaction log dosyasının truncate (temizlenemez) edilemez hale gelmesinin nedeni, SQL Server’ın bu işlemleri büyük, atomic (bölünemez) ve tamponlanmış (fully logged) bir işlem olarak ele almasıdır. Neden log truncate edilemez?

1. Index Rebuild, Tek Bir Büyük Transaction’dır
• ALTER INDEX REBUILD komutu tek bir büyük transaction başlatır.
• Bu transaction tamamlanana kadar ilgili log kayıtları aktif kalır (VLF’ler active olur).
• Log dosyasının truncate edilebilmesi için transaction’ın tamamlanması veya rollback edilmesi gerekir.

2. Recovery Model ve Log Tutulması
• Veritabanınız FULL veya BULK_LOGGED recovery modelindeyse, SQL Server, tüm değişiklikleri transaction log’da saklar.
• Bu, gerektiğinde veritabanını tam olarak geri yükleyebilmek içindir.
• Bu modelde, sadece BACKUP LOG komutu log’u truncate eder — ama aktif transaction varsa işe yaramaz.

3. Log Backup Yapsanız Bile Aktif Transaction Varsa Temizlik Olmaz.
• BACKUP LOG işlemi sadece “kullanılmayan” (inactive) log alanını temizler.
• Index rebuild işlemi tamamlanmadığı sürece, ilgili log segmentleri aktif kalır → truncate yapılamaz.

Sonuç olarak:

Index rebuild büyük bir transaction olarak çalışır. Transaction bitene kadar log truncate edilemez. Recovery modeli FULL veya BULK_LOGGED Tüm değişiklikler log’lanır. Aktif VLF’ler varsa Log backup işe yaramaz, dosya büyümeye devam eder

Database üzerine sağ tıklayıp properties ekranından Files kısmına geliyorum.

Gelen ekranda Autogrowth/ Maxsize bölümünde ldf dosyamızın autogrowth değerine set edebiliriz. Autogrowth bölümünde bulunan 3 noktaya tıklıyorum. Gelen ekranda ldf dosyamızın işletim sisteminden tahsis edeceği alanın MB veya Percent(Yüzde) cinsinden tahsis edilmesini sağlıyoruz.

Dikkat edersek oluşturmuş olduğum veritabanının başlangıç boyutu 8 MB herhangi bir işlem yapılmazsa bu 8 MB’ın tahsis edildiğini gösterir. Aşağıdaki resimde Enable autogrowth değeri bu log dosyasının aktif çalıştığını gösterir aynı anda sadece bir log dosyasına yazılır.

File Growth bölümünde In Percent veya In Megabytes ifadesi ldf dosyamızın büyüme cinsini ve miktarını belirtmektedir. Aşağıdaki resimde percent olarak belirlenmiş olsaydı. Veritabanı bir transaction geldiğinde işletim sisteminden başlangıçta belirlenen size değerinin  %10’u kadar bir disk alanı alır. (Yukarıdaki resimde dikkat edersek başlangıçtaki size değeri 8 MB.) Buda yavaşlığa ve birden fazla sanal dosya oluşmasını sağlar.

Yukarıda belirttiğim gibi autogrowth değerinin yüzde değil MB cinsinden olması gerekmektedir. Ana sebep vlf sayısının az olmasını sağlamak ve log dosyası büyüdükten sonra yüzde olarak tahsis edilecek değer çok büyükte olabilir. In Megabytes kısmında  vlf sayımız az olsun diye 10-20 GB’lar cinsinden değer set etmemiz alan tahsis ederken beklemeye sebep olacaktır. Genellikle tahsis edilen alan 1024 MB veya 512 MB cinsindendir.

Şimdi gelelim tekrar konumuza Growth değerimizi 50 MB yapıyoruz. Bu bize ldf her büyüdüğünde 50 MB alan al anlamına gelmektedir. Buda her 50 MB alan için bir vlf oluşmasına sebep olacaktır.

Not: Maximum file size ise ldf dosyamızın ulaşacağı maksimum alanı limitleyerek biliriz. İkinci bir not belirtmek gerekirse ldf dosyası maksimum 2 TB olmaktadır.

Aşağıdaki komut ile veritabanı altındaki vlf sayısını bulabiliriz. İlgili veritabanı altında çalıştırılması gerekmektedir.

DBCC LOGINFO()

Bu bilgilerden sonra şimdi instance altında bulunan tüm veritabanlarının vlf sayısını  aşağıdaki komut ile bulabiliriz.

SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO

Yukarıdaki tabloda In-active vlf sayısının 0 gözükmesi bu database’in shrink olmamasına sebep olacak. Sebebi ise aktif vlf’lerin şuan kullanımda olmasıdır. Log backup alındıktan sonra shrink işlemine tabi tutulursa vlf sayımız düşmektedir. İn-active vlf sayımız fazla olan databaselerde log backup alındıktan sonra shrink işlemine tabi tutulması vlf sayısının düşmesine sebep olacaktır. Vlf sayımız düşmüyorsa tekrardan 1-2 defa log backup alınması gerekmekte. Bir sonraki makalemizde shrink işleminde yukarıdaki işaretlemiş olduğum database’deki vlf count sayısını düşürmüş olacağım. İlgili link‘ten makaleye ulaşabilirsiniz.

Şunu da belirtmek gerek kullanılmayan vlf’leri yani sanal page alanlarını işletim sistemine iade etmek performans açısından bize çok katkı sağlamayacağı kanaatindeyim. Bunun birden fazla sebebi var birinci ldf boyutum örneğin 200 gb’a çıkmışsa biz shrinkte yapsak bile tekrar o boyuta çıkma ihtimali çok yüksek. Ben ldf içerisindeki aktif olmayan bu alanı işletim sistemine iade edersem veritabanı tekrar o boyuta ulaşmak istediğinde işletim sisteminden alan tahsis edecek bu alanı tahsis edene kadar log dosyasına bir veri yazılamayacak buda gecikmelere sebep olacak. Eğer kullanılmayan vlf’leri log dosyamızı shrink ederek işletim sistemine iade etmezsek  yeni bir transaction geldiğinde kullanılmayan vlf’lerin kullanılmasına sebep olacak buda  yeni bir alan için disk alanı tahsis etmemesine sebep olacak ve  bize iyi bir performans sağlayacak. Sadece tüm vlf’ler kullanılıyorsa işletim sisteminden yeni disk alanı tahsis edecek. Makalenin başında belirtiğim insert update veya veritabanın recovery süresini uzatır kısmında ise veritabanı kendini tutarlı hale getirmek için tüm vlf’leri okur. Active olmayan vlf’ler hızlı okunmaktadır.

Kontrolsüz bir vlf büyümesi varsa müdahale edilmezi gerekmektedir.

Şimdi bir sonraki makalemiz olan Log Dosyasını Shrink etme işlemine geçelim.

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

Mü’min erkeklere söyle, gözlerini haramdan sakınsınlar, ırzlarını korusunlar. Bu davranış onlar için daha nezihtir. Şüphe yok ki, Allah onların yaptıklarından hakkıyla haberdardır. Nûr -30