MSSQL Server Data File Boyutlarını Mail ile Bildirme

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

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

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

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

USE [DBAMON];
GO

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

    DECLARE @MailBody NVARCHAR(MAX);
    DECLARE @profile_name NVARCHAR(100) = 'SQLProfile_NAME';
    DECLARE @recipient NVARCHAR(200) = 'DB_yonetimi@xxx.gov.tr';
    DECLARE @ThresholdGB DECIMAL(18,2) = 10240.00; -- 10 TB 

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

    INSERT INTO @DataInfo (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 = 'ROWS' and CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(18,2))  >10240
      AND database_id > 4; -- sistem db'lerini atla (master, model, msdb, tempdb)

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

    ------------------------------------------------------------
    -- 3) 10 TB üzeri data dosyalarını al ve HTML mail oluştur
    ------------------------------------------------------------
    IF EXISTS (SELECT 1 FROM @DataInfo 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>⚠️ MDF/NDF Dosya Boyutu Uyarısı (10 TB Üzeri)</h3>
            <p><b>Sunucu:</b> ' + @@SERVERNAME + '<br />
               <b>Kontrol Tarihi:</b> ' + CONVERT(NVARCHAR(20), GETDATE(), 120) + '</p>
            <table>
                <tr>
                    <th>Veritabanı</th>
                    <th>Data 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 @DataInfo
        WHERE SizeGB > @ThresholdGB
        ORDER BY SizeGB DESC;

        SET @MailBody = @MailBody + 
            N'</table>
              <br><p style="color:gray;">Bu e-posta SQL Server DATA 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 MDF/NDF Dosya Boyutu Uyarısı - 10 TB Ü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_DataFileKontrol;
DELETE FROM dbo.DataFileTakip
WHERE KontrolTarihi < DATEADD(DAY, -30, GETDATE());

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

“İnsanlara merhamet etmeyene Allah merhamet etmez.” (Müslim)

Bir yanıt yazın