January 20, 2021 at 2:58 pm
I need to configure the below alerts manually for multiple servers
Backup failure, disk space , long running queries, deadlock , memory utilisation >80, primary datafile growth etc,
any scripts is helpful
January 20, 2021 at 3:57 pm
If you want all of that, you should buy a monitoring product where you don't have to worry about what the code actually does.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2021 at 7:10 pm
I need to configure the below alerts manually for multiple servers
Backup failure, disk space , long running queries, deadlock , memory utilisation >80, primary datafile growth etc,
any scripts is helpful
It would be a rare case where your memory utilization would be less than 80%.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
January 21, 2021 at 9:47 am
Of course, a monitoring product is nice and fine and $$
But, if you are on a 0 budget, you could start the KISS way:
( we always install as DBA DBMail profile )
function Add-SQLDBADefaultAlerts {
<#
.SYNOPSIS
Add default SQL Server system alerts
.DESCRIPTION
set up Alerts for Severities 17–25
Configure SQL Server to forward instances of:
- Alerts for Severities 17–25
- Common IO errors (824, 825, 826)
to a SQL Server Agent Operator.
.PARAMETER <Parameter-Name>
If bound parameters, no need to put them overhere
.EXAMPLE
Add-SQLDBADefaultAlerts -SQLInstance myserver\myinstance ;
.NOTES
-Date 2015-05-29 - Author Bijnens Johan
.LINK
http://sqlmag.com/blog/sql-server-database-corruption-part-v-storage-problem-alerts
.LINK
http://sqlmag.com/blog/alwayson-ags-and-sql-server-jobs-setting-failover-alerts
.LINK
http://technet.microsoft.com/en-us/library/hh847834.aspx
#>
[CmdletBinding()]
Param([parameter(Mandatory=$true,HelpMessage="SQL Server Instance Name")][string]$SQLInstance
)
# Check module SQLPS
if ( !(get-module -name SQLPs ) ) {
# save original location
Push-Location
# SQLPs will set the current location to SQLSERVER:\ !!
# -DisableNameChecking -> avoid remarks abount non-discouverable function names
import-module -name SQLPs -DisableNameChecking | out-null
#reset current location to original location
Pop-Location
}
#Interrupt when errors occur
Trap {
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('Trapped error at line [{0}] : [{1}]' -f $ErrorLineNumber, $err.Message );
write-error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-error $err.Message
};
# End the script.
break
}
$OperatorName = 'DBAGroup' ;
$EmailAddress = 'Mailbox.dba@Your.Company' ;
$SQLSystemAlerts= @{};
#-- Alert Names start with the name of the server
$SQLSystemAlerts += @{ 17 = 'Alert - Sev 17 Error: Query Out of Resources' } ;
$SQLSystemAlerts += @{ 18 = 'Alert - Sev 18 Error: DBengine error' } ;
$SQLSystemAlerts += @{ 19 = 'Alert - Sev 19 Error: Fatal Error in Resource' } ;
$SQLSystemAlerts += @{ 20 = 'Alert - Sev 20 Error: Fatal Error in Current Process' } ;
$SQLSystemAlerts += @{ 21 = 'Alert - Sev 21 Error: Fatal Error in Database Process' } ;
$SQLSystemAlerts += @{ 22 = 'Alert - Sev 22 Error Fatal Error: Table Integrity Suspect' } ;
$SQLSystemAlerts += @{ 23 = 'Alert - Sev 23 Error: Fatal Error Database Integrity Suspect' } ;
$SQLSystemAlerts += @{ 24 = 'Alert - Sev 24 Error: Fatal Hardware Error' } ;
$SQLSystemAlerts += @{ 25 = 'Alert - Sev 25 Error: Fatal Error' } ;
$SQLSystemAlerts += @{ 823 = 'Alert - Error: 823 - Read/Write Failure' } ;
$SQLSystemAlerts += @{ 824 = 'Alert - Error: 824 - Page Error' } ;
$SQLSystemAlerts += @{ 825 = 'Alert - Error: 825 - Read-Retry Required' } ;
try {
$db = Get-SqlDatabase -ServerInstance $SQLInstance -Name msdb -ErrorAction Stop ;
if ( $db.Parent.Edition -like '*express*' ) {
# SQL Express doesn't support SQLAgent -> bail out
throw $('[{0}] - Express edition not supported' -f $SQLInstance ) ;
}
# Select SQLAgent
$SQLAgent = $db.parent.JobServer ;
# Check if SQLAgent has been configured to use DBMail
if ( $SQLAgent.DatabaseMailProfile -eq '' ) {
Write-Warning 'Setting SQLAgent DatabaseMailProfile' ;
$SQLAgent.DatabaseMailProfile = 'DBA' ;
$SQLAgent.Alter();
$SQLAgent.refresh();
}
else {
Write-verbose 'Setting SQLAgent DatabaseMailProfile already configured' ;
}
$Operator = $SQLAgent.Operators | where name -eq $OperatorName
#Check if operator needs to be created
if ( !( $Operator ) ) {
$Operator = New-Object Microsoft.SqlServer.Management.Smo.Agent.Operator $SQLAgent, $OperatorName
$Operator.EmailAddress = $EmailAddress ;
$Operator.Enabled = $true;
# actually create the operator on the instance;
$Operator.Create() ;
Write-Warning $('SQLAgent Operator [{0}] created at [{1}].' -f $OperatorName, $db.Parent.DomainInstanceName );
}
$xCtr = 0 ;
foreach ($k in ($SQLSystemAlerts.keys.GetEnumerator())) {
$xCtr ++ ;
$pct = $xCtr * 100 / $SQLSystemAlerts.count;
Write-Progress -Activity $( 'Progressing [{0}] - [{1}]' -f $SQLInstance, $k ) -Status $SQLSystemAlerts.$k -PercentComplete $pct ;
$TargetAlert = $('{0} - {1}' -f $db.Parent.DomainInstanceName , $SQLSystemAlerts.$k ) ;
if ( !( $SQLAgent.Alerts | where Name -eq $TargetAlert )) {
$a = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert $SQLAgent, $TargetAlert ;
# no added value
#$a.CategoryName = $AlertCategory ;
if ( $k -lt 26 ) {
$a.Severity = $k ;
}
else {
$a.MessageID = $k ;
}
$a.IncludeEventDescription = [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail ;
# First create the alert then add the operator and alter
$a.Create() ;
$a.AddNotification( $OperatorName, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail);
$a.Alter() ;
Write-Warning $('Added [{0}] ' -f $TargetAlert );
}
}
#Close connection
$db.Parent.ConnectionContext.Disconnect();
}
catch {
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('Trapped error at line [{0}] : [{1}]' -f $ErrorLineNumber, $err.Message );
write-Error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-error $err.Message
};
}
}
# clear-host
Add-SQLDBADefaultAlerts -SQLInstance $TargetSQLInstance ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply