SQL Server 2017 Log Backup Yeni DMV ler ile Monitoring

Reading Time: 4 minutes

SQL Server 2017 ile gelen bir diğer yenilik de Log Backup ların büyüme oranları tahmin edilebilir hale getirildi.

Bu DMV ler ;

  • sys.dm_db_log_info
  • sys.dm_db_log_stats

yeni DMV ler Virtual Log File lar hakkında da bilgi vermektedirler.

Kısaca Log File lara değinecek olursak ,

bildiğiniz üzere veritabanı default iki file ile oluşur. Bunlardan biri Data File diğeri ise Log File dır.Data File dosyası db ilk oluştuğunda .mdf uzxantılı olarak oluşmaktadır. Log file dosyası ise .ldf uxzantılı dosyadır.Log File dosyaları işlem günlüğü yad akayıt dosyası olarak da ele alabilirsiniz.İşlem günlüğünde tüm işlemlerin başlangıç ve bitişi, DML ve DDL işlemleri , Extend ve Page ayırma / kapatma işlemleri, tablo üzerinde Index oluşturma silme işlemleri vb. bilgileri barındırır.

SQL Server’ın mimarisini anlamak için Transaction Log’un önemini kavramak gerekir. Her veritabanında en az 1 tane transaction log dosyası bulunur. Birden fazla transaction log dosyası eklenebilir fakat aynı anda sadece 1 log dosyasına yazma işlemi yapılır. Yani performansı arttırmak maksadıyla  farklı bir diskte transaction log dosyası oluşturmak işe yaramaz. Ama bazen transaction log dosyasının olduğu diskte yer kalmayınca başka bir diskte 2.bir transaction log dosyası oluşturarak bu sorunu aşarız.

 

Örneğin SQL Server’ın çalıştığı sunucunuzda beklenmedik bir hata oluştu ve kapandı ya da restart oldu. Böyle bir durumda SQL Server açılırken veritabanını tutarlı bir yapıya getirebilmek için transaction log dosyasını kullanır. Transaction Log dosyasından aldığı bilgiyle tamamlanmış transaction ları roll forward yaparak data file’a yazar, tamamlanmamış transactionları da roll back yapar. Bu şekilde veritabanı tutarlılığını garanti altına almış olur.

SQL Server Transaction Log dosyası bir ya da birden fazla sanal log dosyasından oluşur. Bu sanal log dosyaları performansı ciddi anlamda etkileyebilir.Bu dosyalara VLF yani Virtual Log File denilmektedir.

VLF hakkında daha detaylı bilgi almak için  VLF Nedir ? makalemi okuyunuz.

 

Tansaction Log Backup ise ,  son transaction log yedeğinin alındığı andan itibaren Veritabanında gerçekleşen tüm işlemlerin (transactions) yedeğini alır. Transaction Log yedeğinin alınabilmesi için önce bir Full yedek alınmış olması gereklidir. Transaction Log backup ları Veritabanı işlemlerine ilişkin zaman içerisinde dönülebilecek bir nokta sunuyor olacaktır. Bu özellikle hatalı ve ya problem teşkil eden veri giriş çıkışlarına yönelik anlık geri dönülebilirlik imkanı sunar. Her log yedeğinden sonra varsayılan olarak sanal log dosyaları üzerindeki işlenmiş log kayıtları silinir (VLF) ve doğal olarak bir sonraki log yedeği kendinden önceki log yedeğinden sonraki işlenmiş log kayıtlarını barındırır.

Aşağıda ki script ile dbo.sqltr_DemoTable adında demo tablomuzu oluşturuyoruz.

Daha sonra veritabanımızın Full Backup ‘ını alıyoruz.

Full Backup sonrası , T-Log Backup alıyoruz.

Şimdi ise T-Log Backup ımızı 1 dakika da bir aldıralım ve sistemin yoğunluk ve Log File üzerinde deki süreçleride ki yoğunluğa göre T-Log backup larımızda ki değişimide görmüş oluruz.

Bir kaç Backup alındıktan sonra farklı bir session da demo tablomuza aşağıda ki script ile data basalım.

Daha sonra 2017 versiyonu ile gelen DMV mizi devreye alacağız. Bu DMV içerisinde ki başlıklara değinelim biraz da ,

Syntax ‘ı :

NOT : SELECT ten sonra STAR’*’ işareti yerine aşağıda açıkladığımız kolonları çekmeniz size yeterli bilgi verecektir.

recovery model : Geçerli veritabanını kurtarma modelidir. 3 tip recovery modelimiz mevcuttur. (Full, Bulk-logged, Simple)

total_log_size_mb: MB türünden toplam işlem günlüğü boyutu bilgisini verir.

active_vlf_count: İşlem günlüğünde yer alan toplam aktif VLF sayısını verir.

log_truncation_holdup_reason: Transaction Log u bekletme sebebini verir. sys.databases ın içerisinde ki log_reuse_wait_desc sutunu ile aynıdır.

log_backup_time: En son işlem günlüğü yedekleme zamanını verir.

log_since_last_log_backup_mb: En son yedeklenen işlem günlüğündne buyana büyüme oranını MB cinsinden verir.

 

Başlattığımız işlemleri SQL Server 2017 ile gelen DMV mizin sonucunu belirli aralıklarla  bir tabloya basmak istersek. Aşağıda ki scripti hazırlayıp ayrı bir sessionda onu çalıştırmamız gerekecek.

Alacağımız datayı loglayacağımız tablomuzu oluşturalım.

Daha sonra 30 saniyede bir bu tabloya loglama yapacak scriptimiz da sıra.

Belirli bir süre bekledikten sonra Log tablomuzu kontrol edelim.

Gördüğünüz gibi işlemlerimizi anlık bu şekilde loglayıp günün belirli saatlerinde log üzerinde ki yoğunluğu rutin bir monitoring süreci ile tespit edip Log file AutoGrowth umuzu , Disk Size ‘ımızı vb. admin süreçlerini elimizdeki veriye dayalı net bir şekilde gerçekleştirebiliyoruz.

 

Kolay Gelsin.

Leave a Reply

Your email address will not be published. Required fields are marked *