Email Notification for DBA Monitoring and Alerts - Powershell by serverku

Last time I wrote about Powershell script to collect the SQL data collector and WMI data collector. Today I would like share one more Powershell script to send an email alerts for collected data statistics and some other counter queries.

1. Create a DBARepository database and following tables there

CREATE DATABASE [DBARepository]
GO
 
USE [DBARepository]
GO

CREATE TABLE [dbo].[SQLServerInstances](
 [InstanceID] [smallint] IDENTITY(1,1) NOT NULL,
 [DBName] [varchar](100) NULL,
 [IsActive] [bit] NULL,
 [UserName] [nvarchar](200) NULL,
 [Password] [nvarchar](200) NULL,
 [ServerName]  [varchar](100) NULL,
 [IsDataCollector] [bit] NOT NULL DEFAULT ((0)),
 [IsEmailAlert] [bit] NOT NULL DEFAULT ((0)),
 [IsIndexBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsSQLObjectBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsDatabaseObjectBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsSchemaBackup] [bit] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[EmailAlerts](
 [AlertID] [smallint] IDENTITY(1,1) NOT NULL,
 [AlertName] [varchar](100) NULL,
 [AlertScript] [varchar](8000) NULL,
 [InstanceIDs] [varchar](20) NULL,
 [EmailTo] [varchar](200) NULL,
 [EmailCC] [varchar](200) NULL,
 [EmailBCC] [varchar](200) NULL,
 [IsActive] [bit] NULL,
 [Schedule] [varchar](30) NULL,
 [Occurrence] [smallint] NULL,
 [StartDate] [smalldatetime] NULL,
 [LastRun] [smalldatetime] NULL,
 [NextRun] [smalldatetime] NULL,
 [FromEmail] [varchar](50) NULL,
 [smtp] [varchar](30) NULL,
 [EndDate] [datetime] NULL,
 [IsWeekendAlert] [bit] NOT NULL DEFAULT ((0)),
 [Note] [varchar](4000) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DBAErrorLog](
 [CounterName] [varchar](200) NULL,
 [ServerName] [varchar](100) NULL,
 [ErrorMessage] [varchar](4000) NULL,
 [ErrorDate] [datetime] NULL,
 [IsSolved] [bit] NULL
) ON [PRIMARY]
 
GO
CREATE FUNCTION [dbo].[GetNextScheduledRunDate] 
(
    @StartDateTime     DATETIME
    ,@PreNextRunDateTime DATETIME
    ,@FrequencyType     VARCHAR(10) -- YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
    ,@FrequencyInterval  INT
 ,@IsWeekendAlert     BIT
)
RETURNS DATETIME
AS
BEGIN

    DECLARE @StartDateTime_   DATETIME    = @StartDateTime
    DECLARE @NextRunDateTime_   DATETIME    = @PreNextRunDateTime
    DECLARE @FrequencyType_   VARCHAR(10) = @FrequencyType
    DECLARE @FrequencyInterval_ INT     = @FrequencyInterval
    DECLARE @CurrentDate_   DATETIME    = GETDATE()
    DECLARE @TempRunDateTime_   DATETIME

    SET @NextRunDateTime_ = ISNULL(@NextRunDateTime_,@StartDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_

    IF @FrequencyType_ = 'YEAR'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(YEAR, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END
    
    IF @FrequencyType_ = 'MONTH'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(MONTH, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'WEEK'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(WEEK, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'DAY'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(DAY, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'HOUR'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(HOUR, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'MINUTE'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(MINUTE, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'SECOND'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(SECOND, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END
 
 
 IF (@IsWeekendAlert = 0 AND @FrequencyType_ IN ('HOUR','MINUTE','SECOND'))
 BEGIN
  IF (DATENAME(WEEKDAY,@NextRunDateTime_) = 'Saturday')
  BEGIN
   SET @NextRunDateTime_ = DATEADD(DAY,2,@NextRunDateTime_)
  END
  ELSE IF (DATENAME(WEEKDAY,@NextRunDateTime_) = 'Sunday') 
  BEGIN
   SET @NextRunDateTime_ = DATEADD(DAY,1,@NextRunDateTime_)
  END
 END
 
    RETURN @NextRunDateTime_
END


GO

[dbo].[EmailAlerts] - Includes the queries with particular schedule which run against specified servers and send data in table format to specified emails, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug

2. Create following PS1 file and schedule in SQL Server Agent or Windows Task

<#
File : EmailAlerts.ps1
Created By : Paresh Prajapati
Created date : 01/09/2015
Purpose : Send an email notification for queries data
#>

Try
{
$Counter = 'EmailAlerts - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT AlertName, AlertScript,InstanceIDs,EmailTo,EmailCC,Schedule,Occurrence,smtp,FromEmail,Note FROM [dbo].[EmailAlerts] WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND ISNULL(NextRun,0) <= GETDATE() AND (GETDATE() BETWEEN StartDate AND EndDate)"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "Server=$DCInstance;Database=$DCDatabase;user id=$DCUserName;password=$DCPassword" 
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi" 

#$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username $DCUserName  -Password $DCPassword -Query $DCQuery
$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$dccn.ConnectionString=$dcconstring 
$dccn.Open()

if ($AlertScripts -ne $null)
{
 foreach ($AlertScript in $AlertScripts) 
 {
  Try
  {
    $DataSet = New-Object System.Data.DataSet
    $Result = New-Object System.Data.DataTable

    $InstanceIDs = $AlertScript.InstanceIDs.ToString()
    $AlertName = $AlertScript.AlertName.ToString()
    $Script = $AlertScript.AlertScript.ToString()
    $EmailTo = $AlertScript.EmailTo.ToString()
    $emailCC = $AlertScript.EmailCC.ToString()
    $Schedule = $AlertScript.Schedule.ToString()
    $Occurrence = $AlertScript.Occurrence.ToString()
    $smtp = $AlertScript.smtp.ToString()
    $FromEmail = $AlertScript.FromEmail.ToString()
    $Note = $AlertScript.Note.ToString()
    
        
    IF ($InstanceIDs -ne $null -and $InstanceIDs -ne "")
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" +  $InstanceIDs + ") AND IsEmailAlert = 1"
    }

    $Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query
    
            
    foreach ($Instance in $Instances) 
    {
     Try
     {   
        $Server = $Instance.ServerName.ToString()
        $DBName = $Instance.DBName.ToString()
        $UserName = $Instance.UserName.ToString()
        $Password = $Instance.Password.ToString()
        
        
        $SqlConnection.ConnectionString = “Server = $Server; Database = $DBName;Integrated Security=sspi" 
        $SqlConnection.Open()
        $SqlCmd.CommandText = $Script
        $SqlCmd.Connection = $SqlConnection

        
            $SqlAdapter.SelectCommand = $SqlCmd
            $SqlAdapter.Fill($DataSet)
            
         
            if($Result -eq $null)
            {
                $Result =  $DataSet.Tables[0].Clone()
                $Result.Clear()
            }

            $Result.Merge($DataSet.Tables[0])
            $DataSet.Tables[0].Clear()


        $SqlConnection.Close()
        #$SqlConnection.Dispose()
      }
     Catch
     {
          #  Write-Host "Error Occured - Instance"

           if ($SqlConnection.State -eq "Open")
           {
           $SqlConnection.Close()
           }

           $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
           $ErrorMessage = $ErrorMessage.replace("'","''")
           $CounterName = $Counter + $AlertName
           $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
           $ErrorLogSQL = $dccn.CreateCommand()
           $ErrorLogSQL.CommandText = $ErrorLogScript
           $ErrorLogSQL.ExecuteNonQuery()

       }
                       
     }
    
    $Style = "
     
    "
    
   
    
    If ($Result.Columns.ColumnName -contains "Alert")
    {
         $IsAlertSet  = 1
    }
    else
    {
        $IsAlertSet  = 0
    }
    
    $PreContent = ""+$AlertName+""+"    "+"Run on: $(Get-Date)

"

    if($Note -ne $null -and $Note -ne "")
    {    
        $PostContect =  "Note:"+"    "+""+$Note+"

"
    }
    else 
    {
        $PostContect =  " "
    }

  
        #$results = $DataSet.Tables[0] | format-table -autosize | out-string
        $results = $Result  | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray| ConvertTo-Html -head $Style -PreContent $PreContent -PostContent $PostContect | out-string 
        

        If ($IsAlertSet -eq 1)
        {
            $results = $results.Replace("High

","")
            $results = $results.Replace("Medium

","")
            $results = $results.Replace("Low

","")
            $results = $results.Replace("Alert

","")        
        }        
        
                
                        
        if($Result -ne $null -and $Result.Rows.Count -gt 0)
        {
            
            $message = new-object System.Net.Mail.MailMessage 
            $message.From = $FromEmail 
            $message.To.Add($emailTo) 

            $message.ReplyTo = "prajapatipareshm@gmail.com"

            if ($results -like '*Class="High"*' -and $emailCC -ne $null -and $emailCC -ne "")
            {
                $message.CC.Add($emailCC) 
                
            }
            
            #$message.Bcc.Add($bccaddress) 
            $message.IsBodyHtml = $True 
            $message.Subject = $AlertName 
            $message.body = $results 
            $smtpServer = $smtp
            $smtp = new-object Net.Mail.SmtpClient($smtpServer) 
            $smtp.Send($message) 
            #$message.dispose()

            $Result.Clear()
            $Result.Dispose()
            $Result = $null
            $results = $null
    
        }




    $NextRunUpdateScript = "UPDATE [dbo].[EmailAlerts] SET [LastRun] = GETDATE(), [NextRun] = dbo.[GetNextScheduledRunDate] (StartDate,NextRun,Schedule,Occurrence,IsWeekendAlert) WHERE AlertName = "+ "'" + $AlertName + "'"
    $NextRunSQL = $dccn.CreateCommand()
    $NextRunSQL.CommandText = $NextRunUpdateScript
    $NextRunSQL.ExecuteNonQuery()
  }
  Catch
  {
   # Write-Host "Error Occured - Script"

   $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
   $ErrorMessage = $ErrorMessage.replace("'","''")
   $CounterName = $Counter + $AlertName
   $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
   $ErrorLogSQL = $dccn.CreateCommand()
   $ErrorLogSQL.CommandText = $ErrorLogScript
   $ErrorLogSQL.ExecuteNonQuery()

   $NextRunUpdateScript = "UPDATE [dbo].[EmailAlerts] SET [LastRun] = GETDATE(), [NextRun] = dbo.[GetNextScheduledRunDate] (StartDate,NextRun,Schedule,Occurrence,IsWeekendAlert) WHERE AlertName = "+ "'" + $AlertName + "'"
   $NextRunSQL = $dccn.CreateCommand()
   $NextRunSQL.CommandText = $NextRunUpdateScript
   $NextRunSQL.ExecuteNonQuery()

  }
    
 }
}
}
Catch
{
   # Write-Host "Error Occured - Main"

   $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
   $ErrorMessage = $ErrorMessage.replace("'","''")
   $CounterName = $Counter + $AlertName
   $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
   $ErrorLogSQL = $dccn.CreateCommand()
   $ErrorLogSQL.CommandText = $ErrorLogScript
   $ErrorLogSQL.ExecuteNonQuery()

}
Finally
{
   $dccn.Close()
}

You can find above script here too.  

3. Sample notification queries
Download sample email alert queries here.

• [AlertName]: Name of Alert [AlertScript]: Queries
• [InstanceIDs]: Comma separated InstancesIds where script will be executed against 
• [EmailTo]: Query output which send to 
• [EmailCC]: Query output which send in CC 
• [EmailBCC]: Query output which send in BCC 
• [FromEmail]: Email notification come from 
• [IsActive]: Flag to disable/enable the counter 
• [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second 
• [Occurrence] : When it will occur or frequency 
• [StartDate] : Start date and time of collector counter 
• [LastRun] : When it last ran, It will update by PS1 script 
• [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script 
• [EndDate]: counter execution end date 
• [IsWeekendAlert]: Notification should be sent in weekend or not 
• [Note]: Remarks which you want to put in notification 
• [smtp]: smtp id for email

 Change Server and Instance Ids in the sample queries. You can add  more counters and schedule it which you want email notification for. I am still enahancing it and you can put your comment for the same if any.

Script to get transactional replication errors for all subscriptions - SQL Server by serverku

Earlier post had same title just with a little bit difference of giving subscribers and all subscribers, so sharing a script which you can watch transactional replication error for all subscribers and get an alert at the time of error raise. Before going ahead with this post I would like you to read my earlier post and share your thoughts there.
So diverting on this post and again sharing script to get the same information, but it for all subscribers, you can also use the parameter of subscriber in where condition to apply filters.

Use distribution
GO
SELECT ma.publisher_db, 
       ma.publication, 
       ma.subscriber_db, 
       msre.time, 
       msre.error_text 
FROM   msrepl_errors msre 
       INNER JOIN msdistribution_history msh 
               ON ( msre.id = msh.error_id ) 
       INNER JOIN msdistribution_agents ma 
               ON ( ma.id = msh.agent_id ) 
ORDER  BY msre.time DESC 

This is just a script which I want to present here. Now how can watch and keep attention when such any error occurs for transactional replication error in last 5 minutes and get an alert for the same with following script which can be scheduled every 5 minutes or whatever frequency as per filter applied of error time in query and run against a distribution database on distributor server,

USE distribution 
GO

DECLARE @body         VARCHAR(max), 
        @subject      VARCHAR(100), 
        @Publication  VARCHAR(50), 
        @SubscriberDB VARCHAR(50), 
        @ErrorText    VATCHAR(max) 

SELECT @Publication = ma.publication, 
       @SubscriberDB = ma.subscriber_db, 
       @ErrorText = msre.error_text 
FROM   msrepl_errors msre 
       INNER JOIN msdistribution_history msh 
               ON ( msre.id = msh.error_id ) 
       INNER JOIN msdistribution_agents ma 
               ON ( ma.id = msh.agent_id ) 
WHERE  msre.time >= Dateadd(minute, -5, Getdate()) 
-- Capturing for last 5  minutes 
ORDER  BY msre.time DESC 

SET @subject = 'Error captured for ' + @SubscriberDB 
               + ' subscriber database of ' + @Publication 
               + ' publication' 
SET @body = @ErrorText 

-- Sending an email 
IF ( @subject IS NOT NULL ) 
  BEGIN 
      EXEC msdb.dbo.Sp_send_dbmail 
        @recipients = 'prajapatipareshm@gmail.com', 
        @subject = @subject, 
        @body = @body, 
        @profile_name = '<Profile name>', 
        @body_format = 'HTML'; 
  END 

Have you scheduled any such script to get an alert and watch for any transaction replication error? Your ideas and thoughts are most welcome.

Configuration Replication failure and retry alert - SQL Server by serverku

As I wrote about replication latency from publisher to distributor and distributor to subscriber for replication and an alert for replication latency, same as we need to have an alert in case of replication failure. I would like to review some of my earlier posts about replication before moving to this post which you may like,

1. Skip Log Reader error in SQL Server replication - How to
2. Review of some replication issues and workaround - SQL Server
3. Could not find the Distributor or the distribution database for the local server-Error while posts a tracer token in Replication

Let the discussion to move ahead and check as how can we set up an alert for in case of replication failure. So going with steps,

1. Create a job which having static code to fire an email as follows and do not need to schedule it,

DECLARE @Subject varchar(50), @body varchar(200)
SET @Subject = 'Replication Alert (Agent Failure)'
SET @body = 'Replication Alert (Agent Failure).<BR />Check the status of agent from replication monitor.'

EXEC msdb.dbo.sp_send_dbmail 
  @recipients = 'prajapatipareshm@gmail.com',
  @subject = @Subject, 
  @body = @body,
  @profile_name = '<Profile Name>',
  @body_format = 'HTML';
  

2. Go to Replication monitor from Replication tab under SQL Server instance.

3. Select the Publication for which you want to receive an alert for failure and move to Warning tab as follows,

4. Go Configure alert and select “Replication : agent failure” as mentioned in below image,

5. For agent property, click on configure button  –> Response tab and select the job which we created earlier in step 1 in Execute job check box,

This is the just steps to configure an alert for replication agent failure. We can also add a notification alert to job operators also, so whenever the replication agent fails, this configuration runs the job and it will fire an email. We can set up the same configuration for replication agent retry too.