Install-DbaAgentAdminAlert

Want to see the source code for this command? Check out Install-DbaAgentAdminAlert on GitHub.
Want to see the Bill Of Health for this command? Check out Install-DbaAgentAdminAlert.

Synopsis

Creates standard SQL Server Agent alerts for critical system errors and disk I/O failures

Description

Creates a predefined set of SQL Server Agent alerts that monitor for critical system errors (severity levels 17-25) and disk I/O corruption errors (messages 823-825). These alerts catch serious issues like hardware failures, database corruption, insufficient resources, and fatal system errors that require immediate DBA attention.

The function automatically creates alerts for severity levels 17-25 and error messages 823-825 unless specifically excluded. It can create missing operators and alert categories as needed, making it easy to establish consistent monitoring across multiple SQL Server instances.

You can specify an operator to use for the alert, or it will use any operator it finds if there is just one. Alternatively, if you specify both an operator name and an email, it will create the operator if it does not exist.

Syntax

Install-DbaAgentAdminAlert
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Category] <String>]
    [[-Database] <String>]
    [[-Operator] <String>]
    [[-OperatorEmail] <String>]
    [[-DelayBetweenResponses] <Int32>]
    [-Disabled]
    [[-EventDescriptionKeyword] <String>]
    [[-EventSource] <String>]
    [[-JobId] <String>]
    [[-ExcludeSeverity] <Int32[]>]
    [[-ExcludeMessageId] <Int32[]>]
    [[-NotificationMessage] <String>]
    [[-NotifyMethod] <String>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Install-DbaAgentAdminAlert -SqlInstance sql1

Creates alerts for severity 17-25 and messages 823-825 on sql1

Required Parameters

-SqlInstance

The target SQL Server instance or instances

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Category

Assigns the alerts to a specific SQL Server Agent alert category for better organization and management. Defaults to 'Uncategorized' if not specified.
Use this to group related alerts together, making it easier to manage alert policies and review alert activity in SQL Server Management Studio. If the category doesn't exist, it will be created
automatically.

-Database

Restricts the alerts to fire only for errors occurring in the specified database. If not specified, alerts will fire for errors in any database on the instance.
Use this when you want to monitor only specific critical databases and avoid noise from test or development databases on the same instance.

-Operator

Specifies the SQL Server Agent operator who will receive notifications when these alerts are triggered. The operator must already exist on the target instance unless you also provide OperatorEmail.
If not specified and only one operator exists on the instance, that operator will be used automatically. Required for alert notifications to function properly.

-OperatorEmail

Creates a new SQL Server Agent operator with this email address if the specified operator name doesn't exist. Must be used together with the Operator parameter.
This allows you to set up both the operator and alerts in a single command when configuring monitoring on a new instance.

-DelayBetweenResponses

Sets the minimum time in seconds that must pass before the alert can fire again for the same condition. Prevents notification spam when errors occur repeatedly.
Use this to avoid flooding your inbox during cascading failures or when the same error occurs multiple times in rapid succession.

-Disabled

Creates the alerts in a disabled state, preventing them from firing until manually enabled. By default, alerts are created in an enabled state.
Use this when you want to set up the alert infrastructure first and enable specific alerts later after testing or validation.

-EventDescriptionKeyword

Filters alerts to fire only when the error message text contains this specific keyword or phrase. Applied in addition to the standard severity and message ID criteria.
Use this to create more targeted alerts that focus on specific error conditions within the broader categories of critical system errors.

-EventSource

Restricts alerts to fire only for errors originating from a specific event source or application. If not specified, alerts will fire regardless of the error source.
Use this to focus monitoring on specific applications or services that interact with your SQL Server instance when you want to isolate alerts from particular systems.

-JobId

Specifies a SQL Server Agent job to execute automatically when any of these alerts fire. Must be a valid job GUID that exists on the target instance.
Use this to trigger automated response scripts, such as collecting diagnostic information, attempting automatic recovery, or escalating to additional monitoring systems.

-ExcludeSeverity

Excludes specific error severity levels from the standard alert creation. By default, the function creates alerts for severity levels 17-25 which cover resource issues, internal errors, and fatal
system problems.
Use this when you want to skip certain severities, perhaps because you already have custom alerts configured for them or they're not relevant to your environment.

-ExcludeMessageId

Excludes specific SQL Server error message IDs from the standard alert creation. By default, the function creates alerts for messages 823-825 which detect disk I/O hardware errors and database
corruption issues.
Use this when you want to skip certain message IDs, perhaps because you have existing custom alerts for these errors or they don't apply to your storage configuration.

-NotificationMessage

Customizes the message content sent to operators when an alert fires. If not specified, SQL Server uses the default system-generated message.
Use this to include specific instructions, contact information, or troubleshooting steps that help your team respond more effectively to critical errors.

-NotifyMethod

Specifies how the operator should be notified when the alert fires. Valid options are 'NotifyEmail', 'Pager', 'NetSend', 'NotifyAll', or 'None'. Defaults to 'NotifyAll'.
Use 'NotifyEmail' for email-only notifications, 'NotifyAll' to use all configured notification methods for the operator, or 'None' to create alerts without notifications (useful for logging only).

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

-Confirm

Prompts you for confirmation before executing any changing operations within the command.