SQL Server’da Alınmayan Log Backup'ların Mail ile Bildirilmesi

Veritabanı yöneticileri için en büyük kabuslardan biri, fark edilmeyen ve kontrolsüz büyüyen Transaction Log dosyalarıdır. “Full Recovery” modelindeki bir veritabanında log yedeği alınmadığında, log dosyası disk dolana kadar büyümeye devam eder ve sistem durma noktasına gelir. Bu makalede, kritik veritabanlarını izleyen ve geciken yedekleri anlık olarak raporlayan bir çözümden bahsedeceğiz.

Büyük ölçekli ortamlarda onlarca veya yüzlerce veritabanını yönetirken, her birinin yedekleme zincirini manuel kontrol etmek neredeyse imkansızdır. Standart SQL Agent işleri (Job) başarılı görünse bile, bazen bir veritabanı “Always On” yapısında replica değiştirmiş olabilir veya tanımlanan yedekleme planı dışında kalmış olabilir. Bu gibi durumları anında tespit etmek için veritabanı motorunun kendi meta verilerini (msdb) sorgulayan bir denetim mekanizmasına ihtiyaç duyulur.

DECLARE @xml NVARCHAR(MAX);
DECLARE @body NVARCHAR(MAX);

-- Sadece yedeği olmayan veya gecikenleri XML formatında tabloya döküyoruz
SET @xml = CAST((
    SELECT 
        td = d.name, '',
        td = d.recovery_model_desc, '',
        td = ISNULL(CONVERT(VARCHAR, MAX(b.backup_finish_date), 120), 'Hiç Alınmadı'), '',
        td = CASE 
            WHEN MAX(b.backup_finish_date) IS NULL THEN 'Log Yedeği Yok' 
            ELSE 'Log Yedeği Gecikmiş' 
        END
    FROM sys.databases d
    LEFT JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'L'
    WHERE sys.fn_hadr_is_primary_replica(d.name) = 1
      AND d.name NOT IN ('master', 'model', 'msdb', 'tempdb')
      AND d.recovery_model_desc <> 'SIMPLE' 
      AND d.state_desc = 'ONLINE'
    GROUP BY d.name, d.recovery_model_desc
    -- FİLTRE: Sadece sorunlu olanları getir
    HAVING MAX(b.backup_finish_date) IS NULL 
       OR DATEDIFF(HOUR, MAX(b.backup_finish_date), GETDATE()) >= 2
    FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));

-- Eğer sonuç varsa mail gövdesini oluştur ve gönder
IF (@xml IS NOT NULL)
BEGIN
Declare @x nvarchar(1000)
set @x= @@SERVERNAME+': '+'Kritik: Log Backup Uyarısı!' 

    SET @body = '<html><body><H3>Kritik: Log Yedeği Eksik Veritabanları</H3>
                <table border="1">
                <tr><th>Veritabanı</th><th>Model</th><th>Son Yedek</th><th>Durum</th></tr>' 
                + @xml + '</table></body></html>';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SqlMailProfile', -- Burayı kendi profil adınla değiştir
        @recipients = 'yunusyucel@jandarma.gov.tr', -- Burayı kendi mailinle değiştir
        @subject = @x,
        @body = @body,
        @body_format = 'HTML';
END
 ELSE 
 BEGIN
     SET @body = '<html><body><H3>Tüm Veritabanların Log Yedeği Alınmış </H3>
                <table border="1">'
     Declare @x2 nvarchar(1000)
set @x2= @@SERVERNAME+': '+'Log Backup Uyarısı'        

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SqlMailProfile', -- Burayı kendi profil adınla değiştir
        @recipients = 'yunusyucel@jandarma.gov.tr', -- Burayı kendi mailinle değiştir
        @subject = @x2,
        @body = @body,
        @body_format = 'HTML';
 END

Paylaşılan kod yapısı, sys.databases ve msdb.dbo.backupset tablolarını birleştirerek veritabanlarının sağlık durumunu analiz eder. Kodun öne çıkan teknik detayları şunlardır:

sys.fn_hadr_is_primary_replica fonksiyonu sayesinde, script sadece yazma işlemlerinin yapıldığı ana sunucuda çalışır. Bu, Always On mimarilerinde gereksiz uyarıların önüne geçer.
SIMPLE recovery modelindeki veritabanları (log yedeği gerektirmez) ve sistem veritabanları otomatik olarak dışlanır.
SQL sonuçları doğrudan XML formatına dönüştürülerek, e-posta içerisinde temiz ve okunaklı bir HTML tablosu oluşturulur.
DATEDIFF(HOUR, …, GETDATE()) >= 2 koşulu ile sadece son 2 saat içinde yedek alınmamış kritik durumlar rapora dahil edilir.
Log backuplar alınmışsa ve mail dönmesi istenmiyorsa scriptin en sonunda bulunan mail yapısı çıkartılır.

Yukarıda verilen script tam çalıştırıldığında tüm veritabanları üzerinde log backup alınmışsa boş bir mesaj dönmektedir.

Sistemin ürettiği çıktı (resimde görüldüğü üzere), hangi veritabanında sorun olduğunu, son başarılı yedeğin ne zaman alındığını ve durumun kritikliğini net bir şekilde ortaya koymaktadır.

Bu yapıyı SQL Agent üzerinde 15 veya 30 dakikalık periyotlarla çalışacak şekilde ayarlamak, disk dolması ve veri kaybı risklerine karşı en etkili erken uyarı sistemlerinden biridir. Yukarıda belirtilen scripti bir procedure yapıyla oluşturup belirli zaman aralıklarında çalıştırılır.

Bu makalede sql server da alınmayan log backupların mail ile bildirilmesi konusuna değinmiş olduk.

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

Huzur Allah’tandır. (Necm-43)

SQL Server’da Alınmayan Log Backup’ların Mail ile Bildirilmesi

Bir yanıt yazın