nützliche SqlServerAlerts

folgendes Script erzeugt im SqlAgent unter Alerts für alle genützen Severities Alerts - wenn man einen Operator definiert kann dieser dann benachrichtigt werden (Properties, Response). Vorraussetzung unter SqlAgentProperties ist unter AlertSystem EnableMailProfile eingestellt ...

@include_event_description_in=1 => alert text wird in emails mitgeschickt

Gleich dannach ist ein Script, dass Email Notifications für alle mit Script 1 erzeugten Alerts erstellt, wobei der Operatorname ganz am Anfang des Scripts im Set richtig eingestellt werden muss - kann man aber auch direkt beim Operator im ssms einstellen


USE [msdb]
GO

/****** Object: Alert [001misc] Script Date: 06/27/2011 16:10:04 ******/
EXEC msdb.dbo.sp_add_alert @name=N'001misc',
@message_id=0,
@severity=1,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
/****** 2-6 Reserved *****************************/

/****** Object: Alert [007notifyStatus] Script Date: 06/27/2011 16:12:16 ******/
EXEC msdb.dbo.sp_add_alert @name=N'007notifyStatus',
@message_id=0,
@severity=7,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [008UserInterventionRequired] Script Date: 06/27/2011 16:27:57 ******/
EXEC msdb.dbo.sp_add_alert @name=N'008UserInterventionRequired',
@message_id=0,
@severity=8,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [009UserDefined] Script Date: 06/27/2011 16:27:57 ******/
EXEC msdb.dbo.sp_add_alert @name=N'009UserDefined',
@message_id=0,
@severity=9,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO


/****** Object: Alert [010Info] Script Date: 06/27/2011 16:13:02 ******/
EXEC msdb.dbo.sp_add_alert @name=N'010Info',
@message_id=0,
@severity=10,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [011DbObjectNotFound] Script Date: 06/27/2011 16:13:02 ******/
EXEC msdb.dbo.sp_add_alert @name=N'011DbObjectNotFound',
@message_id=0,
@severity=11,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/** 12 unused ******/
/****** Object: Alert [013UserTransactionSyntaxError] Script Date: 06/27/2011 16:13:02 ******/
EXEC msdb.dbo.sp_add_alert @name=N'013UserTransactionSyntaxError',
@message_id=0,
@severity=13,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [014lowPermission] Script Date: 06/27/2011 16:25:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'014lowPermission',
@message_id=0,
@severity=14,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object: Alert [015SqlSyntaxError] Script Date: 06/27/2011 16:13:02 ******/
EXEC msdb.dbo.sp_add_alert @name=N'015SqlSyntaxError',
@message_id=0,
@severity=15,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [016MiscUserError] Script Date: 06/27/2011 16:13:02 ******/
EXEC msdb.dbo.sp_add_alert @name=N'016MiscUserError',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO


/****** Object: Alert [017LowResources] Script Date: 06/27/2011 16:12:49 ******/
EXEC msdb.dbo.sp_add_alert @name=N'017LowResources',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
/****** Object: Alert [018InternalError] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'018InternalError',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [019FatalResource] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'019FatalResource',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
/****** Object: Alert [020FatalCurProc] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'020FatalCurProc',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [021FatalDbProc] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'021FatalDbProc',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

/****** Object: Alert [022FatalTableIntegr] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'022FatalTableIntegr',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
/****** Object: Alert [023FatalDbIntegr] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'023FatalDbIntegr',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
/****** Object: Alert [024FatalHardwareError] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'024FatalHardwareError',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
/****** Object: Alert [025FatalError] Script Date: 06/27/2011 16:10:21 ******/
EXEC msdb.dbo.sp_add_alert @name=N'025FatalError',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=3600,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO


/**************************** UND NOTIFICATIONS EINSTELLEN, OPERATOR NAME RICHTIGSTELLEN !!!! ****************************************************************



USE [msdb]
GO

DECLARE @Operator as nvarchar(MAX)
SET @Operator='operatorname';

EXEC dbo.sp_add_notification @alert_name = N'011DbObjectNotFound',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [001misc] Script Date: 06/27/2011 16:10:04 ******/
EXEC dbo.sp_add_notification @alert_name = N'001misc',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** 2-6 Reserved *****************************/

/****** Object: Alert [007notifyStatus] Script Date: 06/27/2011 16:12:16 ******/
EXEC dbo.sp_add_notification @alert_name =N'007notifyStatus',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/****** Object: Alert [008UserInterventionRequired] Script Date: 06/27/2011 16:27:57 ******/
EXEC dbo.sp_add_notification @alert_name =N'008UserInterventionRequired',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/****** Object: Alert [009UserDefined] Script Date: 06/27/2011 16:27:57 ******/
EXEC dbo.sp_add_notification @alert_name =N'009UserDefined',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email



/****** Object: Alert [010Info] Script Date: 06/27/2011 16:13:02 ******/
EXEC dbo.sp_add_notification @alert_name =N'010Info',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/****** Object: Alert [011DbObjectNotFound] Script Date: 06/27/2011 16:13:02 ******/
EXEC dbo.sp_add_notification @alert_name =N'011DbObjectNotFound',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/** 12 unused ******/
/****** Object: Alert [013UserTransactionSyntaxError] Script Date: 06/27/2011 16:13:02 ******/
EXEC dbo.sp_add_notification @alert_name =N'013UserTransactionSyntaxError',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/****** Object: Alert [014lowPermission] Script Date: 06/27/2011 16:25:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'014lowPermission',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [015SqlSyntaxError] Script Date: 06/27/2011 16:13:02 ******/
EXEC dbo.sp_add_notification @alert_name =N'015SqlSyntaxError',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [016MiscUserError] Script Date: 06/27/2011 16:13:02 ******/
EXEC dbo.sp_add_notification @alert_name =N'016MiscUserError',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email



/****** Object: Alert [017LowResources] Script Date: 06/27/2011 16:12:49 ******/
EXEC dbo.sp_add_notification @alert_name =N'017LowResources',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [018InternalError] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'018InternalError',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/****** Object: Alert [019FatalResource] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'019FatalResource',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [020FatalCurProc] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'020FatalCurProc',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/****** Object: Alert [021FatalDbProc] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'021FatalDbProc',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email


/****** Object: Alert [022FatalTableIntegr] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'022FatalTableIntegr',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [023FatalDbIntegr] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'023FatalDbIntegr',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [024FatalHardwareError] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'024FatalHardwareError',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email

/****** Object: Alert [025FatalError] Script Date: 06/27/2011 16:10:21 ******/
EXEC dbo.sp_add_notification @alert_name =N'025FatalError',
@operator_name = @Operator,
@notification_method = 1 ; -- 1 = Email
GO