in my never ending quest to automate more monitoring I have added this to my pre-production deployment package. It assumes a Operator group of the name DBgroup has been created to transmit the alerts.
Also Database Mail must be set up on the agent.
USE [msdb]
GO
DECLARE @RemoveExisting as bit = 1; --Set to 1 to purge any existing
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('model','master', 'msdb','tempdb')
DECLARE @DBname nvarchar(max) = '';
DECLARE @AlertName nvarchar(max) = '';
DECLARE @AlertText nvarchar(max) = '';
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AlertName = @DBname + N'_LogPercentUsed';
SET @AlertText = N'Databases|Percent Log Used|' + @DBname + '|>|90';
IF EXISTS(select id from sysalerts where name = @AlertName) AND @RemoveExisting = 1
EXEC msdb.dbo.sp_delete_alert @AlertName;
IF NOT EXISTS(select id from sysalerts where name = @AlertName)
BEGIN
EXEC msdb.dbo.sp_add_alert @name= @AlertName ,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=1715,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition= @AlertText
, @job_id=N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name= @AlertName, @operator_name=N'DBGroup', @notification_method = 1;
END
FETCH NEXT FROM db_cursor INTO @DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor
