How to Send an Email Notification When SQL Server Service Restarts

Monitoring SQL Server service restarts is essential for maintaining database availability and troubleshooting unexpected issues. In this post, I’ll show you how to automate email notifications whenever SQL Server services start.

Why Monitor Service Restarts?

Unexpected SQL Server service restarts could indicate issues like:

• Server reboots
• Maintenance activities
• Resource constraints
• Crashes or failures

Getting an instant email notification helps database administrators (DBAs) quickly investigate the reason for the restart.

SQL Script to Send Email on Service Restart

Below is a simple T-SQL script that sends an email when SQL Server services restart.

USE msdb

GO

-- Declare variables for email content

DECLARE @ServerName VARCHAR(128),

        @ComputerNamePhysicalNetBIOS VARCHAR(128),

        @Datetime DATETIME,

        @EmailRecipients VARCHAR(512),

        @EmailSubject VARCHAR(128),

        @MessageBody VARCHAR(512)

-- Assign values to variables

SELECT @ComputerNamePhysicalNetBIOS = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR(128)),

       @ServerName = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(128)),

       @Datetime = GETDATE(),

       @EmailRecipients = 'bajeyudu@sqldbanow.com', -- Add multiple emails using a semicolon (;)

       @EmailSubject = SQL Server Services Have Been Started!!!'

-- Construct the email body

SELECT @MessageBody = 'SQL Server services have been started on a SQL Server Instance named ' + @ServerName + CHAR(13) +

                      'running on Windows server ' + @ComputerNamePhysicalNetBIOS + '.' + CHAR(13) + CHAR(13) +

                      'Investigate the service restart if it has not been communicated.'

-- Send email using Database Mail

EXEC sp_send_dbmail

    @recipients = @EmailRecipients,

    @subject = @EmailSubject,

    @body = @MessageBody,

    @body_format = 'TEXT'

How to Automate This?

1. Enable Database Mail:
   Ensure Database Mail is configured in SQL Server. 

2. Create a SQL Server Agent Job:
• Open SQL Server Agent → Jobs → New Job.
• Set the job name (e.g., "Monitor SQL Service Restarts").
• Add a new step and paste the above script.
• Schedule it to run at server startup (under Schedules, choose Start automatically when SQL Server Agent starts)

Automating Suspect Pages Monitoring in SQL Server with Email Alerts

Introduction

Database corruption is one of the most critical issues in SQL Server, and detecting suspect pages early can help prevent data loss and downtime. SQL Server logs suspect pages in msdb.dbo.suspect_pages whenever it encounters corruption issues like torn pages, checksum failures, or I/O errors. In this article, I’ll share a simple yet effective SQL script that automates the detection of suspect pages and sends an HTML-formatted email report to database administrators.

SQL Script for Suspect Pages Alert

The following SQL script checks for suspect pages in SQL Server and sends an email alert with the details when any are found. Additionally, it deletes suspect page records older than 90 days to maintain a clean log.

DECLARE @count INTEGER;

DECLARE @tableHTML NVARCHAR(MAX);

DECLARE @subj NVARCHAR(100);

SELECT @count = COUNT(1) FROM msdb.dbo.suspect_pages;

SET @subj = 'Suspect Pages Found in ' + @@SERVERNAME;

SET @tableHTML =

    N'<H1>Suspect Pages Found in ' + @@SERVERNAME + ', details are below.</H1>' +

    N'<table border="1">' +

    N'<tr><th>Database ID</th><th>Database</th>' +

    N'<th>File ID</th><th>File</th><th>Page ID</th>' +

    N'<th>Event Desc</th><th>Error Count</th><th>Last Updated</th></tr>' +

    CAST((SELECT td = sp.database_id, '',

                 td = d.name, '',

                 td = sp.file_id, '',

                 td = mf.physical_name, '',

                 td = sp.page_id, '',

                 td = CASE

                         WHEN sp.event_type = 1 THEN '823 or 824 error other than a bad checksum or a torn page'

                         WHEN sp.event_type = 2 THEN 'Bad checksum'

                         WHEN sp.event_type = 3 THEN 'Torn Page'

                         WHEN sp.event_type = 4 THEN 'Restored (The page was restored after it was marked bad)'

                         WHEN sp.event_type = 5 THEN 'Repaired (DBCC repaired the page)'

                         WHEN sp.event_type = 7 THEN 'Deallocated by DBCC'

                     END, '',

                 td = sp.error_count, '',

                 td = sp.last_update_date

          FROM msdb.dbo.suspect_pages sp

          INNER JOIN sys.databases d ON d.database_id = sp.database_id

          INNER JOIN sys.master_files mf ON mf.database_id = sp.database_id AND mf.file_id = sp.file_id

          FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +

    N'</table>';

IF @count > 0

BEGIN

    EXEC msdb.dbo.sp_send_dbmail

        @recipients = N'bandaruajeyudu@outlook.com',

        @body = @tableHTML,

        @subject = @subj,

        @body_format = 'HTML',

        @profile_name = 'SQLMail';

END

DELETE FROM msdb.dbo.suspect_pages WHERE last_update_date < GETDATE() - 90;

--Note: To ensure this script works correctly, you need to configure Database Mail in SQL Server.

How to Monitor SQL Server Failed Jobs and Get Alerts via Email

Managing and monitoring SQL Server jobs is crucial for maintaining the health and performance of your SQL Server environment. One of the key aspects is to ensure that failed jobs are promptly reported and addressed. 

Below is a script that creates a report of failed SQL Server jobs and sends it via email. This script can be particularly useful for DBAs to automate the monitoring process.

CREATE TABLE #Failed_Jobs (

    Status VARCHAR(10) NOT NULL,

    [Job Name] VARCHAR(100) NOT NULL,

    [Step ID] VARCHAR(5) NOT NULL,

    [Step Name] VARCHAR(30) NOT NULL,

    [Start Date Time] VARCHAR(30) NOT NULL,

    Message NVARCHAR(MAX) NOT NULL

);

-- Insert failed job details

INSERT INTO #Failed_Jobs

SELECT 

    'FAILED' AS Status, 

    CAST(sj.name AS VARCHAR(100)) AS "Job Name",

    CAST(sjs.step_id AS VARCHAR(5)) AS "Step ID",

    CAST(sjs.step_name AS VARCHAR(30)) AS "Step Name",

    CAST(

        REPLACE(CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date)), 102), '.', '-') + ' ' +

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 3, 0, ':'), 6, 0, ':')

    AS VARCHAR(30)) AS "Start Date Time",

    sjh.message AS "Message"

FROM msdb.dbo.sysjobs sj

JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id

JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id AND sjs.step_id = sjh.step_id

WHERE sjh.run_status <> 1

  AND CAST(sjh.run_date AS FLOAT) * 1000000 + sjh.run_time > 

      CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS FLOAT) * 1000000 + 70000  -- yesterday at 7 AM

UNION

SELECT 

    'FAILED', 

    CAST(sj.name AS VARCHAR(100)) AS "Job Name",

    'MAIN' AS "Step ID",

    'MAIN' AS "Step Name",

    CAST(

        REPLACE(CONVERT(VARCHAR, CONVERT(DATETIME, CONVERT(VARCHAR, sjh.run_date)), 102), '.', '-') + ' ' +

        STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, sjh.run_time), 6), 3, 0, ':'), 6, 0, ':')

    AS VARCHAR(30)) AS "Start Date Time",

    sjh.message AS "Message"

FROM msdb.dbo.sysjobs sj

JOIN msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id

WHERE sjh.run_status <> 1 AND sjh.step_id = 0

  AND CAST(sjh.run_date AS FLOAT) * 1000000 + sjh.run_time > 

      CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS FLOAT) * 1000000 + 70000  -- yesterday at 7 AM

-- Check if there are failed jobs

DECLARE @cnt INT;  

SELECT @cnt = COUNT(1) FROM #Failed_Jobs;    

IF (@cnt > 0)

BEGIN

    DECLARE @strsubject VARCHAR(100);

    SELECT @strsubject = 'Check the following failed jobs on ' + @@SERVERNAME;

    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML = 

        N'<H1>Failed Jobs Listing - ' + @@SERVERNAME + '</H1>' + 

        N'<table border="1" cellspacing="0" cellpadding="4">' +

        N'<tr><th>Status</th><th>Job Name</th><th>Step ID</th>' +

        N'<th>Step Name</th><th>Start Date</th><th>Message</th></tr>' +

        CAST((

            SELECT 

                N'<tr><td>' + Status + 

                N'</td><td>' + [Job Name] + 

                N'</td><td>' + [Step ID] + 

                N'</td><td>' + [Step Name] + 

                N'</td><td>' + [Start Date Time] + 

                N'</td><td>' + Message + N'</td></tr>'

            FROM #Failed_Jobs

            FOR XML PATH(''), TYPE

        ) AS NVARCHAR(MAX)) + 

        N'</table>';

    EXEC msdb.dbo.sp_send_dbmail

        @from_address = 'BANDARUAJEYUDU@OUTLOOK.com',

        @recipients = 'BANDARUAJEYUDU@OUTLOOK.com',

        @subject = @strsubject,

        @body = @tableHTML,

        @body_format = 'HTML',

        @profile_name = 'SQLMail';

END

-- Drop temp table

DROP TABLE #Failed_Jobs;

By automating the reporting of failed jobs, DBAs can quickly address issues and ensure the smooth operation of SQL Server jobs.

SQL Server Failover Notification via Database Mail

When a failover occurs in a SQL Server Always On Availability Group or a Failover Cluster Instance (FCI), both the SQL Server and SQL Server Agent services restart on the new node. To ensure timely awareness of failovers, we can set up an automated email alert using Database Mail.

Below is a SQL script that sends an email notification whenever a failover happens:

DECLARE @importance AS VARCHAR(6)

DECLARE @body AS NVARCHAR(1000)

DECLARE @Subject  AS NVARCHAR(1000)

DECLARE @InstanceName AS VARCHAR(100)DECLARE @NodeName AS NVARCHAR(100)

DECLARE @recipients VARCHAR(100)

SELECT  @InstanceName =@@SERVERNAME

SELECT  @NodeName = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(200))

SELECT  @Subject  = 'Fail over Happened for Instance '+@InstanceName

SELECT  @body = 'Fail over Happened for Instance '+@InstanceName + '. This instance is currently running on the node '+@NodeName

SELECT  @importance ='High'

EXEC msdb.dbo.sp_send_dbmail

     @profile_name ='sqlmail',

         @recipients ='bajeyudu@SQLDBANOW.com;',

     --@recipients=@recipientsList,

     @subject = @subject ,

     @body = @body,

     @body_format = 'HTML' ,

     @importance=@importance

SQL Server Alert: Trigger to Notify Job Enable/Disable Status Changes

As a SQL Server DBA, keeping track of job status changes is crucial, especially when jobs are disabled or enabled without prior notice. To enhance monitoring, I’ve created a SQL Server Trigger that automatically sends an email notification whenever a job's enabled/disabled status changes.

Here's the complete trigger script:

USE [msdb]

GO

CREATE TRIGGER [dbo].[JobStatusAlert]

   ON [dbo].[sysjobs]

   AFTER UPDATE

AS

BEGIN

    SET NOCOUNT ON;

    

    -- Check if job is enabled/disabled

    DECLARE @MailBody VARCHAR(300)

    

    -- Check if job status is changed (enabled/disabled)

    IF (SELECT TOP 1 CASE WHEN d.enabled = i.enabled THEN 0 ELSE 1 END

        FROM deleted d INNER JOIN inserted i

        ON d.job_id = i.job_id) = 1

    BEGIN

        -- Get session detail and create a message

        SELECT TOP 1 @MailBody = 'Job "'

            + i.name

            + '" is recently '

            + CASE WHEN i.enabled = 0 THEN ' DISABLED ' ELSE ' ENABLED ' END

            + ' by user '

            + login_name

            + ' with session id '

            + CAST(c.session_id AS VARCHAR(3))

            + ' and host name '

            + host_name + ' at '

            + CONVERT(VARCHAR(50), last_request_end_time, 109)

        FROM sys.dm_exec_connections c

        INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id

        CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle)

        CROSS APPLY inserted i

        WHERE text LIKE '%exec msdb.dbo.sp_help_job%'

        AND text NOT LIKE '%SELECT c.session_id'

        ORDER BY last_read DESC;

        

        -- Send mail to DBA Team

        EXEC msdb.dbo.sp_send_dbmail

            @recipients='bajeyudu@SQLDBANOW.com', -- Change mail address accordingly

            @subject = 'Job Status Changed at SQLDBANOWDB01 Server',

            @profile_name = 'Sqlmail', -- Change profile name accordingly

            @body = @MailBody;

    END

END

GO