Sunday, September 28, 2014

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
;
 

image

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'

image

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'
 

image

Setup Alert

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

image

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)'

image

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

image

image

1 comments:

  1. 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

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates