SCRIPT: How to monitor SQL Server restarts?

•  Create Stored Procedure on the master database:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[notify_SQLServerEngineRestartNotification]
AS
BEGIN
-- Detect if SQL Server was restarted.
DECLARE @UpTimeDays int
       ,@SQLServerStarted varchar(20)
       ,@rc int
       ,@msg varchar(1000)

SET @UpTimeDays = (select DATEDIFF(D, create_date, GETDATE()) from master.sys.databases where name = 'tempdb')
IF @UpTimeDays = 0
BEGIN
    SET @SQLServerStarted = (select CONVERT(varchar(20), create_date, 113) from master.sys.databases where name = 'tempdb')
    SET @msg = 'The SQLEngine <b>' + @@SERVERNAME + '</b> was restarted on <b>' + @SQLServerStarted + '</b>'
    EXEC @rc = msdb.dbo.sp_send_dbmail
        @recipients = 'YOUR_EMAIL',
        @importance = 'high',
        @subject = 'SQLEngine Restart Notification',
        @body_format = 'html',
        @body = @msg,
        @exclude_query_output = 1
    IF @rc = 1 RAISERROR('sp_send_dbmail failed', 16, 1)
END
END

• Mark the procedure to run as a startup:

USE MASTER
GO
EXEC SP_PROCOPTION notify_SQLServerEngineRestartNotification, 'STARTUP', 'ON'
GO

•  Check if the procedure is added to the startup:

SELECT [name]
FROM sysobjects
WHERE type = 'P'
AND OBJECTPROPERTY(id, 'ExecIsStartUp') = 1

• Enable 'default trace enabled' if it is not enabled.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled';
GO