When managing SQL Server AlwaysOn Availability Groups and/or standard Database Mirroring, setting up SQL Alerts should be standard protocol. (See this SQL Server Pro article for more background on this.) These types of Alerts can be configured to send e-mails any time the defined event occurs which is great for being notified of unexpected failovers and errors. However, the mass of e-mails generated during a regular planned maintenance that involves one or more failovers can be a little annoying. (Or maybe that’s just me… But if you’re reading this, maybe that’s just you as well. 🙂 I work with dozens of servers that have multiple Availability Groups and Mirrored Databases across them. Whenever we perform planned maintenance, the first thing I do is to disable all Mirroring and Availability Group related Alerts to spare my Inbox from the onslaught of unnecessary SQL Alert e-mails notifying me of the failovers. To do this quickly and efficiently, I’ve written a PowerShell script to disable/enable multiple Alerts on any number of servers automatically.

To execute the script, two input files are necessary. First, create a “Alert_Servers.txt” file containing all the servers with Alerts to be disabled. The Alert_Servers.txt file should be saved in the same directory as the PowerShell script and should look like this (Just replace “Server#” with your real server names.):

Secondly create a “Alerts.txt” file containing the specific Alerts you want to disable/enable. This file should also be saved in the same directory as the PowerShell script and should look similar to this:

In the example above, I’ve added all the specific Mirroring and Availability Group Alerts we set up as standard on all our servers. You can get a list of all configured Alerts on your servers by simply running this query:

SELECT name FROM msdb.dbo.sysalerts;

Just add new lines to this file for each Alert you’d like to disable/enable.

Finally, save the PowerShell Script below as a .ps1 file. (In the example shown below, I’ve named the file ToggleAlerts.ps1)

if (!(Get-PSSnapin -Name SQLServerCmdletSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SQLServerCmdletSnapin100}
if (!(Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SqlServerProviderSnapin100}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended") | Out-Null

$Enabled = read-host "Enable or Disable? Please enter '1' or '0'"

if ($Enabled.ToString() -ne 1 -and $Enabled.ToString() -ne 0)
{
Write-Output "Only 1 and 0 are valid entries."
Exit
}

$ServerList = Get-Content Alert_Servers.txt
$AlertList  = Get-Content Alerts.txt

Write-Output "Please wait..."

foreach ($Server in $ServerList)
{
foreach ($Alert in $AlertList)
{
$Query = "IF EXISTS (SELECT 1 FROM msdb..sysalerts WHERE name = '$Alert')
EXEC msdb.dbo.sp_update_alert
@name = '$Alert'
,@enabled = $Enabled"

Invoke-Sqlcmd -Server $Server -Database "msdb" -Query $Query
}
}

if ($Enabled -eq 1)
{Write-Output "Alerts Enabled!"}

if ($Enabled -eq 0)
{Write-Output "Alerts Disabled!"}

Once you’ve created the ToggleAlerts.ps1 and the two text files, open a PowerShell window and execute the script. In the example below, I’ve saved all three files to the folder “C:\PowerShell\ToggleAlerts”. Enter “0” to disable alerts prior to the start of maintenance:

Once your failovers and maintenance are complete, run the same script again but enter “1” instead to re-enable the Alerts:

That’s all it takes to save your Inbox during a regular maintenance window!

When working with many related SQL Servers it can be helpful to review SQL Error Logs on multiple servers all at once while also filtering for certain types of error messages. There are many use cases for this script, but the most common use case I’ve encountered is to check for failed logins and connection timeouts after an Availability Group or Mirrored Database failover. I work in an environment with multiple Always On Availability Groups with dozens of webservers that connect to them via Listeners. When we do regular maintenance such as Windows Patching, we manually failover our Availability Groups as part of the patching process. With one run of this PowerShell script, we can ensure there are no failed logins or connection issues once the failovers and maintenance have been completed. Some of our Availability Groups span up to five Replicas, so there have been occasions where our Logins are out of sync.

To execute the script, two input files are necessary. First, create a “Servers.txt” file containing all the servers with error logs to review. The Servers.txt file should be saved in the same directory as the PowerShell script and should look like this (Just replace “Server#” with your real server names.):

Secondly create a “Error_Filter.txt” file containing the specific error messages you’re interested in searching for. This file should also be saved in the same directory as the PowerShell script and should look similar to this:

In the example above, we’re looking for any error message containing “login failed” or “connection timeout”.  Any number of different filter criteria can be included in the search by simply adding a new line to this file.

Finally, save the PowerShell Script below as a .ps1 file. (In the example shown below, I’ve named the file CheckErrorLogs.ps1)

if (!(Get-PSSnapin -Name SQLServerCmdletSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SQLServerCmdletSnapin100}
if (!(Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SqlServerProviderSnapin100}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended") | Out-Null

$DateLimit = read-host "Please enter a minimum date/time in the following format:
[YYYY-MM-DD HH:MM]   i.e. 2015-01-01 15:30
Leave blank to omit the date time filter.

Enter Date"

$ServerList = Get-Content Servers.txt
$ErrorList  = Get-Content Error_Filter.txt
$Results=@()

foreach ($Server in $ServerList)
{
foreach ($Error in $ErrorList)
{
$Query = "SET NOCOUNT ON
CREATE TABLE #ERRORLOG (LogDate DATETIME, ProcessInfo VARCHAR(64), [Text] VARCHAR(MAX))
INSERT INTO #ERRORLOG
exec sp_readerrorlog
SELECT @@SERVERNAME AS 'ServerName', LogDate, [Text] FROM #ERRORLOG
WHERE [Text] LIKE '$Error'
AND LogDate > '$DateLimit'
DROP TABLE #ERRORLOG"

$Results += Invoke-Sqlcmd -Server $Server -Database "master" -Query $Query
}
}

$Results | Out-GridView

Once you’ve created the CheckErrorLogs.ps1 and the two text files, open a PowerShell window and execute the script. In the example below, I’ve saved all three files to the folder “C:\PowerShell\ReadSQLErrorLogs”.

Enter a minimum date to filter for recent error messages only, or leave it blank to get all error messages in the current error log.

Finally, the results are output to a grid view for easy review and sorting.

Hopefully, this will save you a lot of time and effort when you need to review multiple SQL error logs!