SQL Server 2017 Log Backup Yeni DMV ler ile Monitoring

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.

USE WideWorldImporters
GO

CREATE TABLE dbo.sqltr_DemoTable
(
Id BIGINT IDENTITY (1,1),
Descriptions VARCHAR(MAX)
)

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

--Full Database Backup
BACKUP DATABASE [WideWorldImporters] TO DISK = N'E:\backup\WideWorldImportersFullBackup.bak' 
WITH FORMAT, NAME = N'WideWorldImportersBackup-Full Database Backup', STATS = 10
GO

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

--Transaction Log Backup
BACKUP LOG [WideWorldImporters] TO DISK = N'E:\backup\WideWorldImportersLogBackup.trn' 
WITH FORMAT, NAME = N'WideWorldImportersLogBackup-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Ş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.

SET NOCOUNT ON
WHILE (1=1)
BEGIN
   BACKUP LOG [WideWorldImporters] TO DISK = N'E:\backup\WideWorldImportersLogBackup.trn'
   WITH FORMAT, NAME = N'WideWorldImportersLogBackup-Log Database Backu', STATS = 10

   WAITFOR DELAY '00:01:00' 
end
GO

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

INSERT INTO dbo.sqltr_DemoTable VALUES ('www.sqlturkiye.com')
GO 10000000

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

Syntax ‘ı :

SELECT *
FROM sys.dm_db_log_stats ( database_id )

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

   SELECT 
      DB_NAME(database_id) AS DBName, 
      recovery_model,
      total_log_size_mb,
      active_log_size_mb,
      active_vlf_count,
      log_truncation_holdup_reason,
      log_backup_time,
      log_since_last_log_backup_mb
   FROM sys.dm_db_log_stats(DB_ID())

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.

CREATE TABLE sqltr_2017Logsts
   (
   DBname varchar(50),
   Recovery_model varchar(20),
   total_log_size_mb float,
   active_log_size_mb float,
   active_vlf_count int,
   log_truncation_holdup_reason nvarchar(100),
   log_backup_time datetime,
   log_since_last_log_backup_mb int
   )
GO

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

SET NOCOUNT ON
WHILE (1=1)
BEGIN
   INSERT INTO sqltr_2017Logsts 
   SELECT 
      DB_NAME(database_id) AS DBName, 
      recovery_model,
      total_log_size_mb,
      active_log_size_mb,
      active_vlf_count,
      log_truncation_holdup_reason,
      log_backup_time,
      log_since_last_log_backup_mb
   FROM sys.dm_db_log_stats(DB_ID())

   WAITFOR DELAY '00:00:30'
END
GO

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

select * from sqltr_2017Logsts

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 *