Share via

MS-SQL Server 2008 R2 and 2005 Agent Restart Alert Job

  • Article

MS-SQL Server 2008 R2 and 2005 Agent Restart Alert Job

Create below Store Procedure.

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[sp_SqlServerRestartNotificationMail]    Script Date: 09/20/2012 23:45:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE   PROC [dbo].[sp_SqlServerRestartNotificationMail]     

--SET NOCOUNT ON   

AS

    DECLARE @msgg AS VARCHAR(100)

    DECLARE @servername AS VARCHAR(50)

    DECLARE @msg AS VARCHAR(500)  

    DECLARE @activenode AS VARCHAR(30)  

 

    SELECT  @servername = @@servername

    SELECT  @activenode = CONVERT(VARCHAR(30), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))

 

    SET @msgg = 'SQL Server Instance ' + @servername + ' has restarted'

    SET @msg = 'SQL Server Instance ' + @servername + ' has restarted at '

        + CAST(GETDATE() AS VARCHAR(30))

        + '. Instance is currently active on ' + @activenode

        + '. You will be getting another email in 5 mins with current status of databases. If you did not receive another email in 5mins then please connect to the server and check Sql server agent status / health check. Thanks. '   

   

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'NYU-Support',

        @recipients = 'vishalk@aditi.com', @subject = @msgg, @body = @msg

    EXEC msdb..sp_start_job 'DB Status after Sql Restart'

GO

 

EXEC sp_procoption N'[dbo].[sp_SqlServerRestartNotificationMail]', 'startup','1'

GO

Note: This store Procedure will create under Master Database. 

1.      Job Name: <ServerName>_Server_Status

Job Alert Code:

USE [msdb]

GO

 

/****** Object:  Job [ServerName_Server_Status]    Script Date: 09/20/2012 23:05:30 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/20/2012 23:05:30 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

 

END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'NYU_Server_Status',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'NYU_Server_Status',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [NYU_Alert_Start]    Script Date: 09/20/2012 23:05:30 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'NYU_Alert_Start',

            @step_id=1,

            @cmdexec_success_code=0,

            @on_success_action=1,

            @on_success_step_id=0,

            @on_fail_action=2,

            @on_fail_step_id=0,

            @retry_attempts=0,

            @retry_interval=1,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N'Exec sp_procoption N''sp_SqlServerRestartNotificationMail'',''startup'', ''true''',

            @database_name=N'master',

            @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'RunAsSQLAgentServiceStartSchedule',

            @enabled=1,

            @freq_type=4,

            @freq_interval=1,

            @freq_subday_type=1,

            @freq_subday_interval=0,

            @freq_relative_interval=0,

            @freq_recurrence_factor=0,

            @active_start_date=20120920,

            @active_end_date=99991231,

            @active_start_time=223201,

            @active_end_time=235959,

            @schedule_uid=N'a8240410-145c-459f-99c1-05df5b707256'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

 

GO

 

 

 

 

Alert Script: ServerName_Job_Alert

USE [msdb]

GO

EXEC msdb.dbo.sp_update_alert @name=N'ServerName_Job_Alert',

            @message_id=0,

            @severity=1,

            @enabled=1,

            @delay_between_responses=0,

            @include_event_description_in=1,

            @database_name=N'',

            @notification_message=N'',

            @event_description_keyword=N'',

            @performance_condition=N'',

            @wmi_namespace=N'',

            @wmi_query=N''

GO

Database Mail Configuration

Open SSMS window -> Management -> database Mail -> Right Click and Select Configure database Mail

Enter Profile Details:

  1. Profile Name: Name of Profile
  2. Description: Description of Profile

Click On Add Button -> New Account.

Enter Database Mail Account Details:

  1. Account Name: Name of Email Alert account
  2. Description: Description about the account.
  3. Email Address: Email address which will send alert emails.
  4. Display Name: Sender Name it will display in Alert Email.
  5. Reply Email: Optional
  6. Server Name : Email Server Name/ IP address
  7. Port Number(SMTP): SMTP port number (default SMTP port number is 25)
  8. SSL: email SSL if you are using secure connection.
  9. Authentication Mode: Use window authentication / Basic authentication.
    1. User Name: Login id of alert email account.
    2. Password: Password of alert email account.

After Enter all details click on Next Button

Select Public Profiles and check the Alert account which you want to use.

After check the Profile change Default Profile to Yes.

Click on Next -> Next -> Finish

2.      Job Name: DB Status after SQL Restart

USE [msdb]

GO

 

/****** Object:  Job [DB Status after Sql Restart]    Script Date: 09/21/2012 16:47:31 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT  @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/21/2012 16:47:31 ******/

IF NOT EXISTS ( SELECT  name

                FROM    msdb.dbo.syscategories

                WHERE   name = N'[Uncategorized (Local)]'

                        AND category_class = 1 )

    BEGIN

        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',

            @type = N'LOCAL', @name = N'[Uncategorized (Local)]'

        IF ( @@ERROR <> 0

             OR @ReturnCode <> 0

           )

            GOTO QuitWithRollback

    END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'DB Status after Sql Restart',

    @enabled = 1, @notify_level_eventlog = 2, @notify_level_email = 0,

    @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0,

    @description = N'No description available.',

    @category_name = N'[Uncategorized (Local)]', @owner_login_name = N'sa',

    @job_id = @jobId OUTPUT

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

/****** Object:  Step [wait]    Script Date: 09/21/2012 16:47:31 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,

    @step_name = N'wait', @step_id = 1, @cmdexec_success_code = 0,

    @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,

    @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1,

    @os_run_priority = 0, @subsystem = N'TSQL',

    @command = N'WAITFOR DELAY ''00:05:00''

 

-- wait for 5 mins so that all DB''s are recovered ater restart

-- ideally it will not take more than a minute

', @database_name = N'master', @flags = 0

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

/****** Object:  Step [Check DB Ststus]    Script Date: 09/21/2012 16:47:31 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,

    @step_name = N'Check DB Ststus', @step_id = 2, @cmdexec_success_code = 0,

    @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,

    @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1,

    @os_run_priority = 0, @subsystem = N'TSQL',

    @command = N'declare @dbstatus varchar(25)

declare @Updatability varchar(25)

declare @Useraccess varchar(25)

declare @db varchar(128)

 

Set nocount ON

 

create table #results(

dbname varchar(50) NULL,

Status varchar(25) null ,

Updatability varchar(25) null ,

UserAccess varchar(20) null)

 

declare dcur cursor local fast_forward

for

 

--following update is  for SLC database servr sql03.

select distinct name from master..sysdatabases

 

--select distinct name from master..sysdatabases

/*select CATALOG_NAME

from INFORMATION_SCHEMA.SCHEMATA*/

 

open dcur

 

fetch next from dcur into @db

 

while @@fetch_status=0

begin

 

set @dbstatus = convert(sysname,DatabasePropertyEx(@db,''Status''))

set @Updatability = convert(sysname,DatabasePropertyEx(@db,''Updateability''))

set @Useraccess = convert(sysname,DatabasePropertyEx(@db,''UserAccess''))

 

 

insert into #results(dbname,Status,Updatability,UserAccess)

 values(@db,@dbstatus,@Updatability,@Useraccess)

 

fetch next from dcur into @db

 

end

close dcur

deallocate dcur

 

 

Select * from #results

 

drop table #results

 

', @database_name = N'master', @output_file_name = N'C:\job_output.txt',

    @flags = 0

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

/****** Object:  Step [Enable Agent XPs]    Script Date: 09/21/2012 16:47:31 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,

    @step_name = N'Enable Agent XPs', @step_id = 3, @cmdexec_success_code = 0,

    @on_success_action = 3, @on_success_step_id = 0, @on_fail_action = 2,

    @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1,

    @os_run_priority = 0, @subsystem = N'TSQL',

    @command = N'IF (charindex(''2005'', @@version) = 0) -- not found

begin

EXECUTE sp_configure ''show advanced options'', 1

RECONFIGURE WITH OVERRIDE

 

EXECUTE sp_configure ''xp_cmdshell'', ''1''

RECONFIGURE WITH OVERRIDE

End', @database_name = N'master', @flags = 0

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

/****** Object:  Step [send db status]    Script Date: 09/21/2012 16:47:31 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,

    @step_name = N'send db status', @step_id = 4, @cmdexec_success_code = 0,

    @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2,

    @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 1,

    @os_run_priority = 0, @subsystem = N'TSQL',

    @command = N'

DECLARE @msgg as varchar(100)

DECLARE @servername as varchar(50)

DECLARE @msg as varchar(500)  

DECLARE @activenode as varchar(30)  

 

 

select @servername =@@servername

 

--Select @activenode=CONVERT(varchar(30), ServerProperty(''ComputerNamePhysicalNetBIOS''))

 

 

Set @msgg = ''SQL Server Instance ''  + @servername + '' Database Status''

 

Set @msg = ''Please find database status on instance '' + @servername + '' . This instance was restarted 5mins back. Kindly connect to server for more details. Thanks. ''   

   

EXEC msdb.dbo.sp_send_dbmail

@profile_name=''NYU-Support'',

@recipients=''vishalk@aditi.com'',

@subject=@msgg,

@body=@msg,

@file_attachments = ''C:\job_output.txt''', @database_name = N'master',

    @flags = 0

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,

    @name = N'RunAsSQLAgentServiceStartSchedule', @enabled = 1, @freq_type = 4,

    @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0,

    @freq_relative_interval = 0, @freq_recurrence_factor = 0,

    @active_start_date = 20120920, @active_end_date = 99991231,

    @active_start_time = 0, @active_end_time = 235959,

    @schedule_uid = N'a8240410-145c-459f-99c1-05df5b707256'

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,

    @server_name = N'(local)'

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF ( @@TRANCOUNT > 0 )

    ROLLBACK TRANSACTION

EndSave:

GO

 

 

3.   

Note:

In above code check yellow highlighted points and modify it as per your requirement.

  • Output File path: Create one .txt file and give the location of file.
  • Receiver Email Address: Email address where email alert will send emails. In case if Instance or server will restart then alert will send to this email address.
  • Database Mail Profile Name: Name of Database Profile which you have mentioned.

Every day at 12:00 AM this query will execute and send the alert email along below report.

Result of This Alert

  1. Whenever SQL Server Instance will restart/ Stop and Start/ Physical machine will restart then this job will execute and it will send two emails.

A.      First Email which will send immediate restart.

B.      Second Email will send after 5 minutes of interval. Here time interval is configurable.

Note: This email will contain report as an attachment which will provide the status of all databases. If any database is having any issue, we can find out though the report

Sample Report: 

  1. Whenever SQL Server Agent will restart / Stop and Start, database Email will send alert email along with status of all databases.