MSSQL Server Log File Boyutlarını Mail ile Bildirme

Bu makalede sql server üzerinde bulunan Log file’ların boyutunu Tablomuza kaydedip belirli bir boyutu geçen Log File boyutunu son kullanıcıya mail ile bildiren yapıyı görmüş olacağız.

Aşağıdaki komut ile tablomuzu oluşturuyoruz.

CREATE TABLE dbo.LogFileTakip
(
    DBName NVARCHAR(255),
    FileName NVARCHAR(255),
    FilePath NVARCHAR(500),
    SizeGB DECIMAL(18,2),
    KontrolTarihi DATETIME DEFAULT GETDATE()
);
GO

Belirtilen procedure ile veritabanı üzerinde bulunan log file kontrolünden sonra mail işlemi gerçekleştirmiş olacağız.

USE [DBAMON];
GO

CREATE OR ALTER PROCEDURE dbo.prc_LogFileKontrol
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @MailBody NVARCHAR(MAX);
    DECLARE @profile_name NVARCHAR(100) = 'SQLProfile_NAME';
    DECLARE @recipient NVARCHAR(200) = 'DByonetimi@xxxx.gov.tr';
    DECLARE @ThresholdGB DECIMAL(18,2) = 500.00; -- 500 GB

    ------------------------------------------------------------
    -- 1) Tüm instance log dosyalarını tek sorguda al (her dosya 1 kez gelir)
    ------------------------------------------------------------
    DECLARE @LogInfo TABLE
    (
        DBName NVARCHAR(255),
        FileName NVARCHAR(255),
        FilePath NVARCHAR(500),
        SizeGB DECIMAL(18,2)
    );

    INSERT INTO @LogInfo (DBName, FileName, FilePath, SizeGB)

    SELECT 
        DB_NAME(database_id) AS DBName,
        name AS FileName,
        physical_name AS FilePath,
        CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(18,2)) AS SizeGB  -- size (8KB pages) -> GB
    FROM sys.master_files
    WHERE type_desc = 'LOG' and CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(18,2))  >500
      AND database_id > 4; -- sistem db'lerini atla (master, model, msdb, tempdb)

    ------------------------------------------------------------
    -- 2) Verileri kalıcı tabloya kaydet (LogFileTakip varsa, aynı yapıyı kullan)
    -- Eğer tablo yoksa önceden oluştur:
    -- CREATE TABLE dbo.LogFileTakip (DBName NVARCHAR(255), FileName NVARCHAR(255), FilePath NVARCHAR(500), SizeGB DECIMAL(18,2), KontrolTarihi DATETIME DEFAULT GETDATE());
    ------------------------------------------------------------
    INSERT INTO dbo.LogFileTakip (DBName, FileName, FilePath, SizeGB, KontrolTarihi)
    SELECT DBName, FileName, FilePath, SizeGB, GETDATE()
    FROM @LogInfo;

    ------------------------------------------------------------
    -- 3) 1.5 TB üzeri log dosyalarını al ve HTML mail oluştur
    ------------------------------------------------------------
    IF EXISTS (SELECT 1 FROM @LogInfo WHERE SizeGB > @ThresholdGB)
    BEGIN
        SET @MailBody = 
        N'<html>
          <head>
            <style>
                body { font-family: Arial; font-size: 13px; color: #333; }
                table { border-collapse: collapse; width: 100%; }
                th, td { border: 1px solid #ccc; padding: 6px; text-align: left; }
                th { background-color: #0078D7; color: white; }
                tr:nth-child(even) { background-color: #f2f2f2; }
                .high { background-color: #ffcccc; font-weight: bold; }
            </style>
          </head>
          <body>
            <h3>⚠️ LDF Dosya Boyutu Uyarısı (500 GB Üzeri)</h3>
            <p><b>Sunucu:</b> ' + @@SERVERNAME + '<br />
               <b>Kontrol Tarihi:</b> ' + CONVERT(NVARCHAR(20), GETDATE(), 120) + '</p>
            <table>
                <tr>
                    <th>Veritabanı</th>
                    <th>Log Dosyası</th>
                    <th>Dosya Yolu</th>
                    <th>Boyut (GB)</th>
                </tr>';

        -- HTML satır ekleme (sadece threshold'u geçenler)
        SELECT @MailBody = @MailBody +
            N'<tr class="high">' +
            N'<td>' + ISNULL(DBName,'') + N'</td>' +
            N'<td>' + ISNULL(FileName,'') + N'</td>' +
            N'<td>' + ISNULL(FilePath,'') + N'</td>' +
            N'<td>' + CAST(SizeGB AS NVARCHAR(20)) + N'</td>' +
            N'</tr>'
        FROM @LogInfo
        WHERE SizeGB > @ThresholdGB
        ORDER BY SizeGB DESC;

        SET @MailBody = @MailBody + 
            N'</table>
              <br><p style="color:gray;">Bu e-posta SQL Server log dosyası izleme sistemi tarafından otomatik gönderilmiştir.</p>
          </body></html>';

        -- Gönder
        EXEC msdb.dbo.sysmail_start_sp;
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = @profile_name,
            @recipients = @recipient,
            @subject = 'SQL LDF Dosya Boyutu Uyarısı - 500 GB Üzeri',
            @body = @MailBody,
            @body_format = 'HTML';
    END
END;
GO

Yukarıdaki procedure oluşturulduktan sonra bir job aracılığı ile belirli zaman aralıklarında çalıştırılması gerekmektedir.

Yukarıdaki procedure yapısını çalıştıran job’ın içirisine ayrı bir step olarak veri boyutunu kaydeden tabloyu belirli aralıklarla belirli bir gün önceki size değerlerini silebiliriz.

EXEC dbo.prc_LogFileKontrol;
DELETE FROM dbo.LogFileTakip
WHERE KontrolTarihi < DATEADD(DAY, -30, GETDATE());

Bu makalede veritabanı üzerinde bulunan LDF dosyalarının boyutunu görmüş olduk. Başka makalede görüşmek dileğiyle..

“Nerede olursan ol Allah’a karşı gelmekten sakın; yaptığın kötülüğün arkasından bir iyilik yap ki bu onu yok etsin. İnsanlara karşı güzel ahlakın gereğine göre davran.” (Tirmizî, Birr, 55.)

Bir yanıt yazın