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.
It is really important questions, I glad that you write about this problem. Just click the link to have a rest with nice company. On the access maturedating.online online
ReplyDeleteIf you are planning a vacation, you certainly know how important the location of your hotel is. See the All bookings hotel search engine. You can book cheap accommodation in different parts of the world, and also read reviews of guests about individual hotels. Book your hotel in the most convenient place for you.
ReplyDelete