SQL Server alerts are a great way to keep tabs on specific performance values in your databases. However, they must be created on a per-value and per-database basis. We DBAs always manage not only lots of databases, but lots of sql server installations (instances), and we add (and sometimes drop) databases, too. So, managing alerts can be a pain because they do not scale.
This TSQL code, hopefully, will make your life a lot easier.
Quick-install Instructions:
1. Make sure Database Mail is set up.
2. Change the code with SQL Server admin emails you want to be alerted.
3. Execute the code once per instance.
The code will create an SP in the MSDB system database, called autocreate_alerts. autocreate_alerts will delete and re-create alerts for each database in that instance. The nice part is that autocreate_alerts will also execute monthly. This means you won't have to do anything new if you add and drop databases from that instance. This code will only re-create alerts with an "-autocreated-" prefix, so it won't touch any alerts you've already created.
Disclaimer #1: If you add or delete an email from the alert list, you will have to re-execute the code on each instance again.
Disclaimer #2: This code works only with Database Mail. It does not work with the older SQL Mail.
This code runs on both SQL Server 2005 and 2008.
I've chosen alerts that were of interest to me. I've made the code as clear as possible, but if you want more alerts, you'll need to study it.
--
-- Execute this code on each SQL server instance.
-- This code creates (and re-creates) specific alerts.
-- This code runs on SQL 2005 and SQL 2008.
-- This code does not work on SQL 2000 or earlier, because it uses
-- SQL Mail (old and busted) and it cannot use Database Mail (new hotness).
-- If Database Mail has not yet been set up, this code will let you know,
-- then halt.
-- If you add operators or alerts, re-run this code to delete the old alerts and
-- re-create them.
-- This code auto-detects databases monthly. You don't need to re-run this
-- code if you add or drop databases.
-- Requirements:
-- You must set up Database Mail, and create a mail profile.
-- You must select a mail profile from within SQL Server Agent properties.
--
use [msdb]
go
-- make sure Database Mail is ready
declare @strDBMailProfile varchar(150)
declare @strHaltError varchar(250)
exec master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
,N'DatabaseMailProfile'
,@strDBMailProfile output
,N'no_output'
if @strDBMailProfile is null
begin
set @strHaltError='A Database Mail profile has not been enabled. '
set @strHaltError=@strHaltError+ 'Set up Database Mail, then right click '
set @strHaltError=@strHaltError+ 'SQL Server agent / properties / '
set @strHaltError=@strHaltError+ 'alert system / enable mail profile.'
print @strHaltError
select @strHaltError as AbortMessage
return
end
go
-- drop and re-create the SP
if exists(select * from sys.objects where object_id=object_id(N'[dbo].[autocreate_alerts]'))
begin
drop procedure [dbo].[autocreate_alerts]
end
go
create procedure [dbo].[autocreate_alerts]
as
-- declare a few tables
declare @tblExcludeDatabases table (
IDExcludeDatabases int identity(1, 1)
,servername varchar(150)
,dbname varchar(150)
)
declare @tblOperators table (
IDOperators int identity(1, 1)
,email varchar(150)
,active bit default 1
)
declare @tblAlertDBCode table (
IDAlertDBCode int identity(1, 1)
,name varchar(250)
,code varchar(2000)
,skip_simplerecoverymodel bit not null
,active bit default 1
)
declare @tblAlertInstanceCode table (
IDAlertInstanceCode int identity(1, 1)
,name varchar(250)
,code varchar(2000)
,active bit default 1
)
declare @tblOperatorDBCode table (
IDOperatorDBCode int identity(1, 1)
,name varchar(250)
,code varchar(2000)
,active bit default 1
)
declare @tblOperatorInstanceCode table (
IDOperatorInstanceCode int identity(1, 1)
,name varchar(250)
,code varchar(2000)
,active bit default 1
)
-- exclude these add servers and databases
-- set the servername to '%' to indicate all servers
-- exclude master, msdb, model and tempdb for all servers
insert into @tblExcludeDatabases (servername, dbname) values ('%', 'master')
insert into @tblExcludeDatabases (servername, dbname) values ('%', 'msdb')
insert into @tblExcludeDatabases (servername, dbname) values ('%', 'model')
insert into @tblExcludeDatabases (servername, dbname) values ('%', 'tempdb')
-- email these operators
-- ADD SQL SERVER ADMINS TO THE @tblOperators TEMPORARY TABLE
insert into @tblOperators (email) values ('sql_server_admin_1@organization.com')
insert into @tblOperators (email) values ('sql_server_admin_2@organization.com')
insert into @tblOperators (email) values ('sql_server_admin_3@organization.com')
-- per DB alert - percent log used alert
declare @strPercentLogUsed_Name varchar(2000)
set @strPercentLogUsed_Name=
'-autocreated-{database}:sql server:databases:% log used>75'
declare @strPercentLogUsed_AddDBAlert varchar(2000)
set @strPercentLogUsed_AddDBAlert=
'exec msdb.dbo.sp_add_alert
@name=QQ{name}QQ
,@message_id=0
,@severity=0
,@enabled=1
,@notification_message=QQThe Percent Log Used performance counter is the percentage of transaction log file space in use. All work in a database halts until the transaction log is written to, and itQQQQs recommended to not allow the log to exceed 80%.QQ
,@delay_between_responses=43200 -- seconds
,@include_event_description_in=1
,@category_name=QQ[Uncategorized]QQ
,@performance_condition=QQSQLServer:Databases|Percent Log Used|{database}|>|75QQ'
declare @strPercentLogUsed_AddDBNotification varchar(2000)
set @strPercentLogUsed_AddDBNotification=
'exec msdb.dbo.sp_add_notification
@alert_name=QQ{name}QQ
,@operator_name=QQ{email}QQ
,@notification_method = 1'
-- instance alert - free pages alert
declare @strFreePages_Name varchar(2000)
set @strFreePages_Name=
'-autocreated-sql server:buffer manager:free pages<640'
declare @strFreePages_AddInstanceAlert varchar(2000)
set @strFreePages_AddInstanceAlert=
'exec msdb.dbo.sp_add_alert
@name=QQ{name}QQ
,@enabled=1
,@delay_between_responses=43200 -- in seconds
,@notification_message=QQThis indicates the total number of free pages. Less than 640 pages (5 meg) indicates physical memory pressure.QQ
,@include_event_description_in=1
,@performance_condition=QQSQLServer:Buffer Manager|Free pages||<|640QQ'
declare @strFreePages_AddInstanceNotification varchar(2000)
set @strFreePages_AddInstanceNotification=
'exec msdb.dbo.sp_add_notification
@alert_name=QQ{name}QQ
,@operator_name=QQ{email}QQ
,@notification_method = 1'
declare @strLockRequests_Name varchar(2000)
set @strLockRequests_Name=
'-autocreated-sqlserver:locks:lock requests/sec:object>1000'
declare @strLockRequests_AddInstanceAlert varchar(2000)
set @strLockRequests_AddInstanceAlert=
'exec msdb.dbo.sp_add_alert
@name=QQ{name}QQ
,@enabled=1
,@delay_between_responses=43200 -- seconds
,@notification_message=QQThis indicates the number of new locks and locks converted per second. Values > 1000 may indicate queries are accessing very large numbers of rows and may benefit from tuning.QQ
,@include_event_description_in=1
,@performance_condition=QQSQLServer:Locks|Lock Requests/sec|Object|>|1000QQ'
declare @strLockRequests_AddInstanceNotification varchar(2000)
set @strLockRequests_AddInstanceNotification=
'exec msdb.dbo.sp_add_notification
@alert_name=QQ{name}QQ
,@operator_name=QQ{email}QQ
,@notification_method = 1'
-- add operator
declare @strAddOperator varchar(2000)
set @strAddOperator=
'exec msdb.dbo.sp_add_operator
@name=QQ{email}QQ
,@enabled=1
,@email_address=QQ{email}QQ
,@category_name=QQ[Uncategorized]QQ'
-- insert to alert per DB
insert into @tblAlertDBCode (name, code, skip_simplerecoverymodel) values (@strPercentLogUsed_Name, @strPercentLogUsed_AddDBAlert, 0)
-- insert into tables to alert each operator per DB
insert into @tblOperatorDBCode (name, code) values (@strPercentLogUsed_Name, @strPercentLogUsed_AddDBNotification)
-- insert to alert for the instance
insert into @tblAlertInstanceCode (name, code) values (@strFreePages_Name, @strFreePages_AddInstanceAlert)
insert into @tblAlertInstanceCode (name, code) values (@strLockRequests_Name, @strLockRequests_AddInstanceAlert)
-- insert into tables to alert each operator for the instance
insert into @tblOperatorInstanceCode (name, code) values (@strFreePages_Name, @strFreePages_AddInstanceNotification)
insert into @tblOperatorInstanceCode (name, code) values (@strLockRequests_Name, @strLockRequests_AddInstanceNotification)
-- begin code here --
declare @tblDBs table (
ID int identity(1, 1)
,dbname varchar(150)
)
declare @tblAlerts table (
alertname varchar(150)
)
declare @strAlert varchar(150)
declare @strDB varchar(150)
declare @strName varchar(250)
declare @strCommand varchar(2000)
declare @strOperator varchar(150)
declare @intIDAlertDBCode int
declare @intIDOperatorDBCode int
declare @bolSkip_SimpleRecoveryModel bit
declare @bolSkipThisDB bit
declare @intIDOperator int
declare @intIDAlertInstanceCode int
declare @intIDOperatorInstanceCode int
declare @strEmail varchar(150)
while exists(select * from @tblOperators where active=1)
begin
select top 1 @strOperator=email from @tblOperators where active=1
update @tblOperators set active=0 where email=@strOperator
if not exists(select [name] from msdb.dbo.sysoperators where [name]=@strOperator)
begin
set @strCommand=@strAddOperator
set @strCommand=replace(@strCommand, 'QQ', '''')
set @strCommand=replace(@strCommand, '{email}', @strOperator)
exec(@strCommand)
while not exists(select * from msdb.dbo.sysoperators where [name]=@strOperator)
begin
set @strOperator=@strOperator
end
end
end
insert into @tblAlerts (alertname)
select [name] from msdb.dbo.sysalerts
where [name] like '-autocreated-%'
while exists(select * from @tblAlerts)
begin
select top 1 @strAlert=alertname from @tblAlerts
delete from @tblAlerts where alertname=@strAlert
set @strCommand='exec msdb.dbo.sp_delete_alert @name=QQ'+@strAlert+'QQ'
set @strCommand=replace(@strCommand, 'QQ', '''')
exec(@strCommand)
while exists(select * from msdb.dbo.sysalerts where [name]=@strAlert)
begin
set @strAlert=@strAlert
end
end
-- iterate over each alert to enable for this instance
update @tblAlertInstanceCode set active=1
while exists(select * from @tblAlertInstanceCode where active=1)
begin
select top 1 @intIDAlertInstanceCode=IDAlertInstanceCode from @tblAlertInstanceCode where active=1
update @tblAlertInstanceCode set active=0 where IDAlertInstanceCode=@intIDAlertInstanceCode
select @strName=name from @tblAlertInstanceCode where IDAlertInstanceCode=@intIDAlertInstanceCode
select @strCommand=code from @tblAlertInstanceCode where IDAlertInstanceCode=@intIDAlertInstanceCode
set @strCommand=replace(@strCommand, 'QQ', '''')
set @strCommand=replace(@strCommand, '{name}', @strName)
exec(@strCommand)
while not exists(select * from msdb.dbo.sysalerts where [name]=@strName)
begin
set @strName=@strName
end
end
-- iterate each operator over each alert
update @tblOperatorInstanceCode set active=1
while exists(select * from @tblOperatorInstanceCode where active=1)
begin
select top 1 @intIDOperatorInstanceCode=IDOperatorInstanceCode from @tblOperatorInstanceCode where active=1
update @tblOperatorInstanceCode set active=0 where IDOperatorInstanceCode=@intIDOperatorInstanceCode
update @tblOperators set active=1
while exists(select * from @tblOperators where active=1)
begin
select top 1 @intIDOperator=IDOperators from @tblOperators where active=1
update @tblOperators set active=0 where IDOperators=@intIDOperator
select @strName=name from @tblOperatorInstanceCode where IDOperatorInstanceCode=@intIDOperatorInstanceCode
select @strCommand=code from @tblOperatorInstanceCode where IDOperatorInstanceCode=@intIDOperatorInstanceCode
select @strEmail=email from @tblOperators where IDOperators=@intIDOperator
set @strCommand=replace(@strCommand, 'QQ', '''')
set @strCommand=replace(@strCommand, '{name}', @strName)
set @strCommand=replace(@strCommand, '{email}', @strEmail)
exec(@strCommand)
end
end
-- iterate over each alert to enable for each database
insert into @tblDBs (dbname)
select [name] from sys.databases as D
left outer join @tblExcludeDatabases as E on (@@servername like E.servername and D.name like E.dbname)
where E.servername is null and E.dbname is null
order by D.name
while exists(select * from @tblDBs)
begin
select top 1 @strDB=dbname from @tblDBs
delete from @tblDBs where dbname=@strDB
update @tblAlertDBCode set active=1
while exists(select * from @tblAlertDBCode where active=1)
begin
select top 1 @intIDAlertDBCode=IDAlertDBCode from @tblAlertDBCode where active=1
update @tblAlertDBCode set active=0 where IDAlertDBCode=@intIDAlertDBCode
set @bolSkipThisDB=0
select @strCommand=code from @tblAlertDBCode where IDAlertDBCode=@intIDAlertDBCode
select @strName=name from @tblAlertDBCode where IDAlertDBCode=@intIDAlertDBCode
select @bolSkip_SimpleRecoveryModel=skip_simplerecoverymodel from @tblAlertDBCode where IDAlertDBCode=@intIDAlertDBCode
if (select cast(count(*) as bit) from sys.databases where name=@strDB and recovery_model_desc='simple' and @bolSkip_SimpleRecoveryModel=1)>0
set @bolSkipThisDB=1
if @bolSkipThisDB=0
begin
set @strName=replace(@strName, '{database}', @strDB)
set @strCommand=replace(@strCommand, 'QQ', '''')
set @strCommand=replace(@strCommand, '{name}', @strName)
set @strCommand=replace(@strCommand, '{database}', @strDB)
exec(@strCommand)
while not exists(select * from msdb.dbo.sysalerts where [name]=@strName)
begin
set @strName=@strName
end
update @tblOperatorDBCode set active=1
while exists(select * from @tblOperatorDBCode where active=1)
begin
select top 1 @intIDOperatorDBCode=IDOperatorDBCode from @tblOperatorDBCode where active=1
update @tblOperatorDBCode set active=0 where IDOperatorDBCode=@intIDOperatorDBCode
update @tblOperators set active=1
while exists(select * from @tblOperators where active=1)
begin
select top 1 @intIDOperator=IDOperators from @tblOperators where active=1
update @tblOperators set active=0 where IDOperators=@intIDOperator
select @strEmail=email from @tblOperators where IDOperators=@intIDOperator
select @strCommand=code from @tblOperatorDBCode where IDOperatorDBCode=@intIDOperatorDBCode
select @strName=name from @tblOperatorDBCode where IDOperatorDBCode=@intIDOperatorDBCode
set @strCommand=replace(@strCommand, 'QQ', '''')
set @strCommand=replace(@strCommand, '{name}', @strName)
set @strCommand=replace(@strCommand, '{database}', @strDB)
set @strCommand=replace(@strCommand, '{email}', @strEmail)
exec(@strCommand)
end
end
end
end
end
go
-- execute the sp to (delete and) create the alerts
exec msdb.dbo.autocreate_alerts
go
-- delete and re-create a job to run the SP monthly, which deletes and re-creates the alerts
go
use [msdb]
go
if exists (select job_id from msdb.dbo.sysjobs_view where name = N'launch msdb.autocreate_alerts sp')
exec msdb.dbo.sp_delete_job @job_name=N'launch msdb.autocreate_alerts sp', @delete_unused_schedule=1
go
declare @jobId BINARY(16)
exec msdb.dbo.sp_add_job @job_name=N'launch msdb.autocreate_alerts sp',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId output
go
exec msdb.dbo.sp_add_jobserver @job_name=N'launch msdb.autocreate_alerts sp'
go
use [msdb]
go
exec msdb.dbo.sp_add_jobstep @job_name=N'launch msdb.autocreate_alerts sp', @step_name=N'launch sp',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'use msdb
go
exec dbo.autocreate_alerts',
@database_name=N'master',
@flags=0
go
use [msdb]
go
exec msdb.dbo.sp_update_job @job_name=N'launch msdb.autocreate_alerts sp',
@enabled=1,
@start_step_id=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=0,
@description=N'',
@category_name=N'[Uncategorized (Local)]',
@notify_email_operator_name=N'',
@notify_netsend_operator_name=N'',
@notify_page_operator_name=N''
go
use [msdb]
go
declare @schedule_id int
exec msdb.dbo.sp_add_jobschedule
@job_name=N'launch msdb.autocreate_alerts sp',
@name=N'monthly',
@enabled=1,
@freq_type=16,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20110120,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_id = @schedule_id output
go
