گزارش خودکار ایمیلی Jobهای ناموفق SQL Server Agent

گزارش خودکار Jobهای ناموفق SQL Server Agent: نظارت هوشمند و به موقع

مدیریت و نظارت بر سلامت پایگاه داده SQL Server شامل اطمینان از عملکرد صحیح تمام Jobهای زمان‌بندی شده SQL Server Agent است. Jobهای SQL Server Agent وظایف حیاتی مانند پشتیبان‌گیری، نگهداری شاخص‌ها، پاکسازی داده‌ها و اجرای ETL را انجام می‌دهند. عدم موفقیت هر یک از این Jobهای می‌تواند منجر به مشکلات جدی در عملکرد، از دست دادن داده‌ها یا نقض توافق‌نامه‌های سطح خدمات (SLA) شود. نظارت دستی بر وضعیت Jobهای به صورت روزانه، به خصوص در محیط‌های با تعداد زیاد سرور یا Jobهای، کاری زمان‌بر و مستعد خطاست.

این مقاله راه حلی قدرتمند و خودکار برای اطلاع رسانی فوری در مورد Jobهای ناموفق SQL Server Agent ارائه می‌دهد. با پیاده‌سازی یک سیستم گزارش‌دهی شبانه، می‌توانید اطمینان حاصل کنید که هر گونه مشکل به سرعت شناسایی و رفع می‌شود، زمان از کار افتادگی را به حداقل رسانده و ثبات عملیاتی را افزایش دهید. این راهکار شامل ایجاد یک رویه ذخیره شده (Stored Procedure) برای جمع‌آوری اطلاعات Jobهای ناموفق و سپس یک شغل SQL Server Agent برای اجرای این رویه و ارسال یک گزارش ایمیلی است.

ایجاد رویه ذخیره شده (Stored Procedure) برای جمع‌آوری Jobهای ناموفق

اولین گام، ایجاد یک رویه ذخیره شده است که وظیفه جستجو در لاگ‌های SQL Server Agent برای یافتن Jobهایی که در 24 ساعت گذشته با شکست مواجه شده‌اند را بر عهده دارد. این رویه، جزئیات مربوط به هر شغل ناموفق، از جمله نام شغل، مرحله‌ای که در آن شکست خورده، و زمان شکست را جمع‌آوری می‌کند. در اینجا اسکریپت مربوط به رویه ذخیره شده آورده شده است:

USE MSDB;
GO

IF OBJECT_ID('dbo.usp_GetFailedAgentJobs') IS NOT NULL
    DROP PROCEDURE dbo.usp_GetFailedAgentJobs;
GO

CREATE PROCEDURE dbo.usp_GetFailedAgentJobs
    @LookbackHours INT = 24
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        j.name AS JobName,
        h.step_name AS StepName,
        h.message AS ErrorMessage,
        msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunDateTime
    FROM
        msdb.dbo.sysjobs AS j
    INNER JOIN
        msdb.dbo.sysjobhistory AS h ON j.job_id = h.job_id
    WHERE
        h.run_status = 0 -- 0 indicates failed
        AND h.step_id  0 -- Exclude job outcome rows (step_id 0)
        AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(hour, -@LookbackHours, GETDATE())
    ORDER BY
        RunDateTime DESC;
END;
GO

این رویه ذخیره شده، `usp_GetFailedAgentJobs`، در پایگاه داده `MSDB` ایجاد می‌شود. پارامتر `@LookbackHours` به شما اجازه می‌دهد تا بازه زمانی مورد نظر برای بررسی Jobهای ناموفق را مشخص کنید که به طور پیش‌فرض 24 ساعت تنظیم شده است. این اسکریپت، جزئیات کاملی از نام شغل، نام مرحله شکست خورده، پیام خطا و زمان اجرای آن را از جداول `sysjobs` و `sysjobhistory` استخراج می‌کند و فیلتر `run_status = 0` برای شناسایی Jobهای ناموفق به کار می‌رود.

پیکربندی شغل SQL Server Agent برای ارسال گزارش ایمیلی

گام بعدی ایجاد یک شغل SQL Server Agent است که رویه ذخیره شده فوق را اجرا کرده و نتایج را از طریق ایمیل ارسال کند. این شغل باید به گونه‌ای پیکربندی شود که به صورت زمان‌بندی شده (مثلاً شبانه) اجرا شود. برای ارسال ایمیل، باید Database Mail در SQL Server پیکربندی شده باشد.

USE MSDB;
GO

DECLARE @jobId UNIQUEIDENTIFIER;

-- Create the SQL Server Agent Job
EXEC msdb.dbo.sp_add_job
    @job_name = N'Daily Failed Jobs Report',
    @enabled = 1,
    @description = N'Sends an email report of failed SQL Server Agent jobs in the last 24 hours.',
    @category_name = N'[Uncategorized (Local)]',
    @owner_login_name = N'sa',
    @job_id = @jobId OUTPUT;

-- Add Job Step 1: Execute Stored Procedure and generate HTML report
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'Generate Report and Send Email',
    @step_id = 1,
    @cmdexec_success_code = 0,
    @on_success_action = 1,
    @on_fail_action = 2, -- If this step fails, the job fails
    @subsystem = N'TSQL',
    @command = N'
    DECLARE @body NVARCHAR(MAX);
    DECLARE @subject NVARCHAR(255);

    SET @subject = N''SQL Server Alert: Daily Failed Jobs Report ('' + @@SERVERNAME + '')'';

    SET @body = (SELECT
        N''<h3>گزارش مشاغل ناموفق SQL Server Agent برای سرور: '' + @@SERVERNAME + N''</h3>'' +
        N''<p>لیست مشاغل ناموفق در 24 ساعت گذشته:</p>'' +
        ISNULL(
            (SELECT
                N''<table border="1"><tr><th>نام شغل</th><th>مرحله</th><th>پیام خطا</th><th>زمان اجرا</th></tr>'' +
                CAST((
                    SELECT
                        td = JobName, N'''',
                        td = StepName, N'''',
                        td = ErrorMessage, N'''',
                        td = CONVERT(VARCHAR(19), RunDateTime, 120)
                    FROM dbo.usp_GetFailedAgentJobs(DEFAULT)
                    FOR XML PATH(''tr''), TYPE
                ) AS NVARCHAR(MAX)) + N''</table>''
            ), N''<p>هیچ شغل ناموفقی در 24 ساعت گذشته یافت نشد.</p>''
        ));

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = N''YourMailProfile'', -- Replace with your Database Mail profile name
        @recipients = N''your_email@example.com'', -- Replace with recipient email address
        @subject = @subject,
        @body = @body,
        @body_format = N''HTML'';
    ',
    @database_name = N'MSDB',
    @flags = 0;

-- Add Job Schedule (e.g., daily at 1 AM)
EXEC msdb.dbo.sp_add_jobschedule
    @job_id = @jobId,
    @name = N'Daily 1AM',
    @enabled = 1,
    @freq_type = 4, -- Daily
    @freq_interval = 1, -- Every day
    @freq_subday_type = 1, -- At specified time
    @freq_subday_interval = 0,
    @freq_relative_interval = 0,
    @freq_recurrence_factor = 0,
    @active_start_date = 20230101, -- Start date
    @active_end_date = 99991231, -- End date
    @active_start_time = 010000; -- 01:00:00 AM

-- Add Job Server
EXEC msdb.dbo.sp_add_jobserver
    @job_id = @jobId,
    @server_name = N'(local)';
GO

این اسکریپت یک شغل SQL Server Agent به نام `Daily Failed Jobs Report` ایجاد می‌کند. این شغل شامل یک مرحله است که رویه ذخیره شده `usp_GetFailedAgentJobs` را اجرا می‌کند و خروجی آن را به فرمت HTML تبدیل کرده و از طریق ایمیل ارسال می‌کند. مطمئن شوید که `YourMailProfile` را با نام پروفایل Database Mail خود و `your_email@example.com` را با آدرس ایمیل دریافت‌کننده مورد نظر جایگزین کنید. زمان‌بندی این شغل برای هر روز ساعت 1:00 بامداد تنظیم شده است که می‌توانید آن را مطابق با نیازهای خود تغییر دهید. اگر در 24 ساعت گذشته هیچ شغل ناموفقی یافت نشود، ایمیل با پیامی مبنی بر عدم وجود مشکل ارسال خواهد شد.

نکات تکمیلی برای مدیریت گزارش Jobهای ناموفق

* **پیکربندی Database Mail:** قبل از اجرای این شغل، اطمینان حاصل کنید که قابلیت Database Mail در SQL Server فعال و به درستی پیکربندی شده باشد.
* **مجوزها:** حساب کاربری که SQL Server Agent با آن اجرا می‌شود، باید مجوزهای لازم برای اجرای رویه ذخیره شده `usp_GetFailedAgentJobs` و ارسال ایمیل را داشته باشد.
* **سفارشی‌سازی قالب ایمیل:** می‌توانید قالب HTML ایمیل را برای نمایش اطلاعات بیشتر یا طراحی بصری بهتر تغییر دهید.
* **پارامتر `@LookbackHours`:** با تغییر پارامتر `@LookbackHours` در فراخوانی رویه ذخیره شده، می‌توانید بازه زمانی بررسی Jobهای ناموفق را سفارشی‌سازی کنید.
* **نظارت بر خود شغل:** مهم است که خود شغل `Daily Failed Jobs Report` نیز به درستی اجرا شود. می‌توانید برای موفقیت یا شکست این شغل نیز اعلان تنظیم کنید.

با پیاده‌سازی این راهکار، شما یک سیستم مانیتورینگ خودکار و کارآمد برای Jobهای SQL Server Agent خود خواهید داشت. این نه تنها بار کاری DBAها را کاهش می‌دهد، بلکه به شما امکان می‌دهد تا به سرعت نسبت به مشکلات واکنش نشان داده و از ثبات و عملکرد پایگاه داده خود اطمینان حاصل کنید. این رویکرد پیشگیرانه برای مدیریت SQL Server، کلید حفظ یک محیط پایگاه داده پایدار و قابل اعتماد است.