1 Setup Database alerts

This is my note for how to setup the Database alert with SQL Server.

Create Database Mail profile

--- Create Database account

EXECUTE msdb.dbo.sysmail_add_account_sp

   @account_name = 'SGC4_MAIL',

   @email_address = 'XXXX@gmail.com',

   @display_name = 'SGC4 SQL Server',

   @mailserver_name = 'smtp.gmail.com', 

   @mailserver_type='SMTP',

   @port=587,

   @username='XXXXX@gmail.com',

   @password='XXXXXX',

   @enable_ssl=1;

- Create a Database Mail profile

XECUTE msdb.dbo.sysmail_add_profile_sp

   @profile_name = 'SGC4_default',

   @description = 'Profile used for administrative mail.' ;

- Add the account to the profile

XECUTE msdb.dbo.sysmail_add_profileaccount_sp

   @profile_name = 'SGC4_default',

   @account_name = 'SGC4_MAIL',

   @sequence_number =1 ;

-- Check database account and profile

elect * from msdb.dbo.sysmail_profile;

elect * from msdb.dbo.sysmail_account;

-- Send test mail

XEC msdb.dbo.sp_send_dbmail 

profile_name='SGC4_default',

recipients = 'XXXX@XXX.COM',

body='This is test from sp_send_dbmail',

subject='This is test from sp_send_dbmail'

-- check the mail result 

elect * from msdb.dbo.sysmail_log

elect mailitem_id,recipients,body,sent_status,sent_date from msdb.dbo.sysmail_allitems

Create operator

USE [msdb]

GO

EXEC msdb.dbo.sp_add_operator @name=N'OncallDBA', 

        @enabled=1, 

        @pager_days=0, 

        @email_address=XXXX@gmail.com'

GO

select * from msdb.dbo.sysoperators

;

Test Operator

You will need to create the mail profiler before this.

exec msdb.dbo.sp_notify_operator @profile_name='SGC4_default',

@name='OncallDBA',

@subject='Test operator.',

@body='Test operator'

Enable SQL Agent

It is very important to bounce the SQLAgent after we setup the mail profiler for the SQL Server.

If that is not being done, we will receive the error “[476] Database Mail is not enabled for agent notifications. ”

--- Setup SQL Agent Mail profile 

USE [msdb]

GO

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1, 

        @databasemail_profile=N'SGC4_default', 

        @use_databasemail=1

GO

select name,value,value_in_use,description from sys.configurations where name='Agent XPs'

GO

EXEC xp_servicecontrol 'querystate','SQLAgent$SGC4SQL2014'

GO

--- use below to enable agent if it is not running

EXEC SP_CONFIGURE 'Agent XPs',1

go

reconfigure with override

go

--- Restart SQL Agent

EXEC xp_servicecontrol 'start','SQLAgent$SGC4SQL2014'

GO

EXEC xp_servicecontrol 'STOP','SQLAgent$SGC4SQL2014'

Setup Alert

Create the new database <TestDB> with initial size 4MB.

Create the alert that will be trigger when the database grow over 100MB.

Database alert will check base on the performance counter.  Then create the test table and insert some data to force the database grow.

--- Create empty test database

USE master;

drop database TestDB;

EXEC msdb.dbo.sp_delete_alert @name=N'DatabaseSizeMonitor';         

create database TestDB;

---  Create alert name 'DatabaseSizeMonitor'

--- It should send out alert email to oncall DBA when the size is over 100MB 

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'DatabaseSizeMonitor', 

        @enabled=1, 

        @delay_between_responses=0, 

        @include_event_description_in=1, 

        @performance_condition=N'Databases|Data File(s) Size (KB)|TestDB|>|104800', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'DatabaseSizeMonitor', @operator_name=N'OncallDBA', @notification_method = 1

GO

--- Check the alert

exec msdb.dbo.sp_help_alert @alert_name = 'DatabaseSizeMonitor'

--- Check the databse size

EXEC sp_databases 

use TestDB;

create table t2 ( c1 char(8000))

go

insert into t2 values ( 'Skye')

go 20000

select * from sys.sysperfinfo 

where instance_name='TestDB' and counter_name = 'Data File(s) Size (KB)'

You can see the performance counter has grow to 163008 and we receive the email for the same value as well.