Comprehensive SQL Server Monitoring Report via Email

By:   |   Updated: 2018-07-09   |   Comments (16)   |   Related: > Monitoring


Problem

It is often required to provide automated reports by email to ensure there are no critical issues going on, so the team can focus on what is most important at any moment. But it is common to rely on Microsoft pre-built stored procedures or other third-party tools that return more than one result table, making it hard to separate the results and filter them as needed. Also, if you need to monitor several things at once (like a daily monitoring report), you can integrate all your queries into a single script and generate a report on the different results presented.

Solution

Using the script below, you’re able to grab multiple results from a stored procedure or multiple statements in a single query, then the script separates and filters them, and sends them in a single email.

Script

Here is a PowerShell script, note that lines 6 to 175 have the database query (as it is run directly in SSMS), so you can omit that part of the script if you’re planning on using your personal script or a stored procedure you already have:

$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "YOURSERVER\YOURINSTANCE"
$database = "master"
$query = @"
-- Services state
USE [master]
GO
CREATE TABLE #ServiceState ([Id] INT IDENTITY(1, 1), [Status] VARCHAR(25), [ServiceName] VARCHAR(25))
INSERT INTO #ServiceState([Status]) EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
INSERT INTO #ServiceState([Status]) EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
INSERT INTO #ServiceState([Status]) EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'
UPDATE #ServiceState SET [ServiceName] = (CASE [Id] WHEN 1 THEN 'MSSQLServer' WHEN 2 THEN 'SQLServerAgent' WHEN 3 THEN 'SQLBrowser' END)
SELECT [ServiceName], [Status] FROM #ServiceState
DROP TABLE #ServiceState

-- Agent jobs
USE [msdb]
GO

CREATE TABLE #Info (
   [JobId] UNIQUEIDENTIFIER, [Name] VARCHAR(128), [Description] VARCHAR(512), [Enabled] INT, [NotifyEmail] INT,
   [NextRun] DATETIME, [SchedFreq] VARCHAR(128), [SubDayFreq] VARCHAR(128), [MaxStep] INT, [LastRunId] INT,
   [LastRun] DATETIME, [PreviousRunId] INT, [LastDurationSeconds] INT, [LastRunFailedStepCount] INT, [LastStepRan] INT,
   [AvgDurationSeconds] INT, [StdDevDurationSeconds] INT)

INSERT INTO #Info ([JobId], [Name], [Description], [Enabled], [NotifyEmail],
   [NextRun], [SchedFreq], [SubDayFreq], [MaxStep])
SELECT [j].[job_id], [j].[name], [j].[description], [s].[enabled], [j].[notify_level_email],
      [dbo].[agent_datetime](CASE WHEN [js].[next_run_date] <> 0 THEN [js].[next_run_date] ELSE [s].[active_start_date] END,
                       CASE WHEN [js].[next_run_time] <> 0 THEN [js].[next_run_time] ELSE [s].[active_start_time] END),
      CASE [s].[freq_type] WHEN 1 THEN 'Once'
                     WHEN 4 THEN 'Daily'
                     WHEN 8 THEN 'Weekly'
                     WHEN 16 THEN 'Monthly'
                     WHEN 32 THEN 'Monthly relative'
                     WHEN 64 THEN 'When agent starts'
                     WHEN 128 THEN 'When computer idle' END,
      CASE [s].[freq_subday_interval]
         WHEN 0 THEN 'Once'
         ELSE 'Every ' + RIGHT([s].[freq_subday_interval], 2) +
            (CASE [s].[freq_subday_type]
               WHEN 1 THEN ' Once'
               WHEN 2 THEN ' Seconds'
               WHEN 4 THEN ' Minutes'
               WHEN 8 THEN ' Hours' END) END,
       (SELECT MAX([step_id]) FROM [sysjobsteps] [st] WHERE [st].[job_id] = [j].[job_id]) [MaxStep]
  FROM [sysjobs] [j]
LEFT JOIN [sysjobschedules] [js] ON [js].[job_id] = [j].[job_id]
LEFT JOIN [sysschedules] [s] ON [s].[schedule_id] = [js].[schedule_id]
 WHERE [j].[enabled] = 1

UPDATE [i]
   SET [i].[LastRunId] = [h].[instance_id],
       [i].[LastRun] = [dbo].[agent_datetime]([h].[run_date], [h].[run_time])
  FROM #Info [i]
INNER JOIN [sysjobhistory] [h] ON [h].[instance_id] = (SELECT MAX([instance_id]) FROM [sysjobhistory] [h2] WHERE [h2].[job_id] = [i].[JobId] AND [h2].[step_id] = 0)

UPDATE [i]
   SET [i].[PreviousRunId] = (SELECT MAX([instance_id]) FROM [sysjobhistory] [h] WHERE [h].[job_id] = [i].[JobId] AND [h].[step_id] = 0 AND [h].[instance_id] < [i].[LastRunId])
  FROM #Info [i]

UPDATE [i]
   SET [i].[LastDurationSeconds] = [t].[LastDurationSeconds],
       [i].[LastRunFailedStepCount] = [t].[LastRunFailedStepCount],
      [i].[LastStepRan] = [t].[LastStepRan]
  FROM #Info [i]
INNER JOIN (
   SELECT [h].[job_id], SUM(CASE WHEN [h].[run_status] NOT IN (1, 2, 4) THEN 1 ELSE 0 END) [LastRunFailedStepCount],
          SUM(CASE WHEN [h].[run_status] = 1 AND [h].[run_duration] > 0 THEN [h].[run_duration]/10000*3600 + ([h].[run_duration]/100)%100*60 + [h].[run_duration]%100 ELSE 0 END) [LastDurationSeconds],
         MAX([step_id]) [LastStepRan]
     FROM [sysjobhistory] [h]
   INNER JOIN #Info [i] ON [i].[JobId] = [h].[job_id] AND [h].[instance_id] > [i].[PreviousRunId] AND [h].[instance_id] < [i].[LastRunId]
   GROUP BY [h].[job_id]) [t] ON [t].[job_id] = [i].[JobId]

UPDATE [i]
   SET [i].[AvgDurationSeconds] = ISNULL(CASE [t].[AvgDurationSeconds] WHEN 0 THEN 1 ELSE [t].[AvgDurationSeconds] END, 1),
       [i].[StdDevDurationSeconds] = ISNULL(CASE [t].[StdDevDurationSeconds] WHEN 0 THEN 1 ELSE [t].[StdDevDurationSeconds] END, 1)
  FROM #Info [i]
INNER JOIN (
   SELECT [job_id],
          AVG([run_duration]/10000*3600 + ([run_duration]/100)%100*60 + [run_duration]%100) [AvgDurationSeconds],
          STDEV([run_duration]/10000*3600 + ([run_duration]/100)%100*60 + [run_duration]%100) [StdDevDurationSeconds]
     FROM [sysjobhistory]
    WHERE [step_id] <> 0
      AND [run_status] = 1
      AND [run_duration] >= 0
   GROUP BY [job_id]) [t] ON [t].[job_id] = [i].[JobId]

SELECT [Name], [Description], [LastRun], [Enabled], [SchedFreq], [SubDayFreq], [NextRun], [LastDurationSeconds], [MaxStep], [LastStepRan],
       (CASE WHEN [LastRunFailedStepCount] > 0 THEN 'One or more steps failed'
             WHEN [LastStepRan] < [MaxStep] THEN 'One or more steps did not run'
            WHEN [NextRun] IS NULL THEN 'Not going to run'
          WHEN [NotifyEmail] = 0 THEN 'Not alerting by email'
          ELSE '' END) [Message],
       (CASE WHEN [LastDurationSeconds] < ([AvgDurationSeconds] - ISNULL([StdDevDurationSeconds], 0))
              OR [LastDurationSeconds] > ([AvgDurationSeconds] + ISNULL([StdDevDurationSeconds], 0))
            THEN CAST([LastDurationSeconds]/[AvgDurationSeconds] AS VARCHAR)+' times average'
          ELSE 'Within average' END) [TimeToComplete],
      (CASE WHEN [NextRun] IS NULL THEN '' ELSE 'USE [msdb]; SELECT TOP 10 [step_id], [message], [run_status], [dbo].[agent_datetime]([run_date], [run_time]) [DateTime], [run_duration] FROM [sysjobhistory] WHERE [job_id] = '''+CAST([JobId] AS NVARCHAR(50))+''' ORDER BY [instance_id] DESC' END) [AdditionalInfo]
  FROM #Info
 WHERE ([LastDurationSeconds] > 15
        AND ([LastDurationSeconds] < ([AvgDurationSeconds] - ISNULL([StdDevDurationSeconds], 0))
          OR [LastDurationSeconds] > ([AvgDurationSeconds] + ISNULL([StdDevDurationSeconds], 0))))
   OR [LastRunFailedStepCount] > 0
   OR [NextRun] IS NULL
   OR [NotifyEmail] = 0
   OR [LastStepRan] < [MaxStep]
ORDER BY [Name]

DROP TABLE #Info

-- Backup status
USE [msdb]
GO
SELECT d.name AS "Database",
       ISNULL(CONVERT(VARCHAR,b.backupdate,120),'NEVER') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
           WHERE type LIKE 'D'
           GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)

-- Error log
USE [master]
GO
EXEC [dbo].[usp_ErrorLogRead]

-- Low disk space
USE [master]
GO
exec dbo.xp_fixeddrives

-- Available memory
USE [master]
GO
SELECT available_physical_memory_kb/1024 as "Total Memory MB",
       available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free"
FROM sys.dm_os_sys_memory

-- Queries needing tuning
USE [master]
GO
SELECT top 10 text as "SQL Statement",
   last_execution_time as "Last Execution Time",
   (total_logical_reads+total_physical_reads+total_logical_writes)/execution_count as [Average IO],
   (total_worker_time/execution_count)/1000000.0 as [Average CPU Time (sec)],
   (total_elapsed_time/execution_count)/1000000.0 as [Average Elapsed Time (sec)],
   execution_count as "Execution Count",
   qp.query_plan as "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
order by total_elapsed_time/execution_count desc

-- Current connections
USE [master]
GO
SELECT spid, kpid, blocked, d.name, open_tran, status, hostname,
cmd, login_time, loginame, net_library
FROM sys.sysprocesses p
INNER JOIN sys.databases d 
 on p.dbid=d.database_id

 -- Requests processing
USE [master]
GO
DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value 
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
WAITFOR DELAY '000:00:10'
SELECT (cntr_value-@BRPS)/10.0 AS "Batch Requests/Sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
"@
$querytimeout = 0 #0 means forever, change it as needed
$filter = @()
$columns = @()
$excludeColumns = @()
$titles = @()
# Here you need to add: filter, columns to retrieve, columns to exclude, and title. This needs to be done for each result table, in the order returned.
$titles += "Services state"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Agent jobs with issues"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Last full backups"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Latest entries from error log"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Free space"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Free memory"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Queries needing tuning"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Current connections"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$titles += "Requests being processed"
$filter += {1 -eq 1}
$columns += ,@('*', ' ')
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$numberOfHeaderRowsToSkip = 1 #for applying the odd/even style to the table
$date = Get-Date
$reportBody = "<h4>Report as of $date</h4>"
$mailServer = "YOURSMTPSERVER"
$mailFrom = "[email protected]"
$mailTo = "[email protected]"
$mailSubject = "$server Daily monitoring"
$reportHeader = "<style>
th {border:solid black 1px; border-collapse:collapse; padding-left:5px; padding-right:5px; padding-top:1px; padding-bottom:1px; background-color:white;}
td {border:solid black 1px; border-collapse:collapse; padding-left:5px; padding-right:5px; padding-top:1px; padding-bottom:1px; color:black; vertical-align: top;}
tr.even {background-color:#D3D3D3;}</style>"
# Import modules
Import-Module SqlPs -DisableNameChecking #uncomment for running it directly in a ps command prompt
# Run query
$result = (Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -querytimeout $querytimeout)
if ($result -eq $NULL -or $result.count -eq 0) {
  exit #if there are no rows, no report is sent and the job completes successfully
}
$tablesFirstRow = @()
$tablesFirstRow += 0
$tablesLastRow = 0
for ($i=0; $i -lt $result.length; $i++) {
   if (-Not $result[$i].Table.Equals($result[$tablesLastRow].Table)) {
      $tablesFirstRow += $i
      $tablesLastRow = $i
   }
}
$tablesFirstRow += $result.length
for ($j=0; $j -lt $tablesFirstRow.length-1; $j++) {
   $firstRow = $tablesFirstRow[$j]
   $lastRow = $tablesFirstRow[$j+1]
   $tmpResult = @()
   for ($i=$firstRow; $i -lt $lastRow; $i++) {
      $tmpResult += $result[$i]
   }
   # apply filtering, convert to HTML with custom header and body
   [string]$innerResult = $tmpResult | where $filter[$j] | select $columns[$j] -ExcludeProperty $excludeColumns[$j] | ConvertTo-HTML -Fragment | Out-String
   [xml]$innerResult = $innerResult.Replace("`0", "") #remove invalid characters that conflict with XML
   for ($i = 0; $i -lt $innerResult.table.tr.count - $numberOfHeaderRowsToSkip; $i++) {
     $class = $innerResult.CreateAttribute("class")
     $class.value = if($i % 2 -eq 0) {"even"} else {"odd"}
     $innerResult.table.tr[$i+$numberOfHeaderRowsToSkip].attributes.append($class) | Out-Null
   }
   $reportBody += "<br/><b>" + $titles[$j] + "</b><br/>" + $innerResult.InnerXml
}
# Send report
$message = New-Object System.Net.Mail.MailMessage $mailFrom, $mailTo
$message.Subject = $mailSubject
$message.IsBodyHTML = $true
$message.Body = ConvertTo-HTML -head $reportHeader -body $reportBody
$smtp = New-Object Net.Mail.SmtpClient($mailServer)
$smtp.Send($message)

Setup

The parts you need to modify are:

  • $server: The name of the server where you want to run the query, can be a remote instance.
  • $database: The name of the database where the stored procedure is located, or where you want to run your queries. Note that this won’t cause any issue if you switch databases within your script.
  • $query: Query to run, in my case I’m running the excellent daily monitoring scripts from this tip, only changed a couple of them to use mine.
  • Agent jobs: I used this tip which provides additional information.
  • Error log: I used this tip which filters common messages.

Some things you need to be aware of is that every script must return a result (even if it is empty) so the titles and filters will match with the query, and also if one of the queries returns a number you must add an empty column to the result (as in line 217, this is due to an error in the PowerShell function ConvertTo-HTML).

  • $titles: This is an array, where you need to add the title to be added above each result table in the email report.
  • $filter, $columns, $excludeColumns: This is an array, where you need to add one row per each result table that is returned. See my other tip on reporting for further details.
  • $mailServer, $mailFrom, $mailTo, $mailSubject, $reportHeader, $reportBody: This depends on your SMTP server and how you want to send the email report.

Result

The script is going to identify the DataTable of each DataRow returned to determine if they belong together or not, effectively separating them.  Here is a sample result you will get using the script above:

Result report received by email
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Pablo Echeverria Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-07-09

Comments For This Article




Thursday, November 15, 2018 - 6:12:44 PM - Rick Back To Top (78267)

Hi,

I got it, the $Result variable name seems to be some type of special reserved value that has a specified type of XML.XMLDocument. When I changed it to $Result1 the whole thing works.  Strange, but it works now.  Maybe I ran something earlier that set the $Result type.  I usually leave PS up as I use it quite often.

Rick 

Wednesday, November 14, 2018 - 9:38:46 AM - Pablo Echeverria Back To Top (78239)

Hi Rick, sorry to hear that, unfortunately you will have to debug the following lines to see what's wrong:

[string]$innerResult = $tmpResult | where $filter[$j] | select $columns[$j] -ExcludeProperty $excludeColumns[$j] | ConvertTo-HTML -Fragment | Out-String
   [xml]$innerResult = $innerResult.Replace("`0", "") #remove invalid characters that conflict with XML

I can only think there are other strange characters that you will have to replace before converting to XML. Please let us know.

Tuesday, November 13, 2018 - 8:24:28 PM - Rick Back To Top (78232)

Hi,

Sorry I did not see that you wrote back, seems the emails from the site are being eaten by O365 spam filters.  I just happened back here and saw you responded.

I got the same error. I took the parens off to see if it would execute the SQL and show the results in the output window.  When I put the parens back and execute I get the Cannot convert value "System.Object[]" to type "Sytem.Xml.XmlDocument" Error

Rick 

Tuesday, November 6, 2018 - 4:32:18 PM - Pablo Echeverria Back To Top (78174)

Hi Rick, the first issue I see is that Invoke-SqlCmd must be inside parenthesis, so it must be: (Invoke-SqlCmd ...)

This is because we need an array, please try changing it minimally and let us know.

Tuesday, November 6, 2018 - 3:22:36 PM - Rick Back To Top (78172)

 Hi Pablo,

It is essentially your code, I stripped it down to help debug, but the issue is the same, if I remove the $Result= (where the error occurs), it will list the results to the output window.

Thanks for your examples and help,

Rick 

 $ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = ""
$database = "master"
$query = @"
-- Services state
USE [master]
GO
CREATE TABLE #ServiceState ([Id] INT IDENTITY(1, 1), [Status] VARCHAR(25), [ServiceName] VARCHAR(25))
INSERT INTO #ServiceState([Status]) EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'MSSQLServer'
INSERT INTO #ServiceState([Status]) EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'
INSERT INTO #ServiceState([Status]) EXEC master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLBrowser'
UPDATE #ServiceState SET [ServiceName] = (CASE [Id] WHEN 1 THEN 'MSSQLServer' WHEN 2 THEN 'SQLServerAgent' WHEN 3 THEN 'SQLBrowser' END)
SELECT [ServiceName], [Status] FROM #ServiceState
DROP TABLE #ServiceState
"@
$querytimeout = 0 #0 means forever, change it as needed
$filter = @()
$columns = @()
$excludeColumns = @()
$titles = @()
# Here you need to add: filter, columns to retrieve, columns to exclude, and title. This needs to be done for each result table, in the order returned.
$titles += "Services state"
$filter += {1 -eq 1}
$columns += ,'*'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors' -split ", ")
$numberOfHeaderRowsToSkip = 1 #for applying the odd/even style to the table
$date = Get-Date
$reportBody = "<h4>Report as of $date</h4>"
$mailServer = ""
$mailFrom = ""
$mailTo = ""
$mailSubject = "$server Daily monitoring"
$reportHeader = "<style>
th {border:solid black 1px; border-collapse:collapse; padding-left:5px; padding-right:5px; padding-top:1px; padding-bottom:1px; background-color:white;}
td {border:solid black 1px; border-collapse:collapse; padding-left:5px; padding-right:5px; padding-top:1px; padding-bottom:1px; color:black; vertical-align: top;}
tr.even {background-color:#D3D3D3;}</style>"
# Import modules
#Import-Module SqlPs -DisableNameChecking #uncomment for running it directly in a ps command prompt
# Run query
$Result =Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -querytimeout $querytimeout
#- DataSet
#        -- DataTables
#        -- DataRows
#get-help Invoke-Sqlcmd -detailed
if ($result -eq $NULL -or $result.count -eq 0) {
  exit #if there are no rows, no report is sent and the job completes successfully
}
$tablesFirstRow = @()
$tablesFirstRow += 0
$tablesLastRow = 0
for ($i=0; $i -lt $result.length; $i++) {
   if (-Not $result[$i].Table.Equals($result[$tablesLastRow].Table)) {
      $tablesFirstRow += $i
      $tablesLastRow = $i
   }
}
$tablesFirstRow += $result.length
for ($j=0; $j -lt $tablesFirstRow.length-1; $j++) {
   $firstRow = $tablesFirstRow[$j]
   $lastRow = $tablesFirstRow[$j+1]
   $tmpResult = @()
   for ($i=$firstRow; $i -lt $lastRow; $i++) {
      $tmpResult += $result[$i]
   }
   # apply filtering, convert to HTML with custom header and body
   [string]$innerResult = $tmpResult | where $filter[$j] | select $columns[$j] -ExcludeProperty $excludeColumns[$j] | ConvertTo-HTML -Fragment | Out-String
   [xml]$innerResult = $innerResult.Replace("`0", "") #remove invalid characters that conflict with XML
   for ($i = 0; $i -lt $innerResult.table.tr.count - $numberOfHeaderRowsToSkip; $i++) {
     $class = $innerResult.CreateAttribute("class")
     $class.value = if($i % 2 -eq 0) {"even"} else {"odd"}
     $innerResult.table.tr[$i+$numberOfHeaderRowsToSkip].attributes.append($class) | Out-Null
   }
   $reportBody += "<br/><b>" + $titles[$j] + "</b><br/>" + $innerResult.InnerXml
}
# Send report
$message = New-Object System.Net.Mail.MailMessage $mailFrom, $mailTo
$message.Subject = $mailSubject
$message.IsBodyHTML = $true
$message.Body = ConvertTo-HTML -head $reportHeader -body $reportBody
$smtp = New-Object Net.Mail.SmtpClient($mailServer)
$smtp.Send($message)

Tuesday, November 6, 2018 - 1:05:37 PM - Pablo Echeverria Back To Top (78171)

Hi Rick, actually I'd need to take a look at your code to see what could be causing the error, can you please include it also?

Tuesday, November 6, 2018 - 12:12:33 PM - Rick Back To Top (78170)

Hi, Using PS command (via Widnows PowerShell ISE) the error below is issued, if I use sp_blitzcache from this article https://www.mssqltips.com/sqlservertip/4973/standardized-table-based-sql-server-monitoring-email-with-powershell/ it works fine. It seems that raw SQL Rows returned by the Invoke-SQLCmd cannot be converted to XML directly. I tried different -OutputAs with similar results. I even just deleted everything except the Services Query and while it wil send it to the output window, it will not convert it to the $Result variable. Do you know what needs to be done to get by this error? Thanks, Rick

Cannot convert value "System.Object[]" to type "System.Xml.XmlDocument". Error: "The specified node cannot be inserted as the valid child of this node, because the specified node is the wrong type." At line:41 char:1 + $Result =Invoke-Sqlcmd -ServerInstance $server -Database $database -Q ...

Tuesday, July 17, 2018 - 8:41:52 AM - Pablo Echeverria Back To Top (76656)

Hi Graham, usp_ErrorLogRead is in another of my tips titled "Read all errors and warnings in the SQL Server Error Log for all versions", and you can also check the one titled "Standardized table based SQL Server monitoring email with PowerShell" to check how to use the $columns and $excludeColumns variables, you just need to specify one of them, hope this helps!

Tuesday, July 17, 2018 - 2:25:31 AM - Graham Okely Back To Top (76655)

A comprehensive report! Nice looking as well.

I can't locate: EXEC [dbo].[usp_ErrorLogRead] Is the source for that stored procedure linked in somewhere?

And I can't get this filtering down to two columns working.

$titles += "Services state"
$filter += {1 -eq 1}
$columns += ,'ServiceName , Status'
$excludeColumns += ,@('RowError, RowState, Table, ItemArray, HasErrors , MaxStep, LastStepRan, Enabled, Description, AdditionalInfo' -split ", ")

Any tips?

Thanks

Graham

Wednesday, July 11, 2018 - 6:59:44 AM - Goran Back To Top (76609)

Even more, the check can be in the same backup job as a step.

Tuesday, July 10, 2018 - 1:18:49 PM - Pablo Echeverria Back To Top (76605)

Hi Mohamed, in that case I recommend you create a database job that executes the query that checks the last full backup date, and if it failed, executes [SQL Check And Error Log Job].

Tuesday, July 10, 2018 - 1:03:12 PM - Mohamed Back To Top (76603)

 

 Hi Pablo,

I have a small question. what if I want to execute a specific job when the job fails for example if FULL BACKUP Failure execute [SQL Check And Error Log Job]. Thank You :)

Monday, July 9, 2018 - 6:19:44 PM - Pablo Echeverria Back To Top (76589)

Hi Mohamed, please post your question here, so everyone with the same problem can benefit from the response. Thank you.

Monday, July 9, 2018 - 8:56:32 AM - Pablo Echeverria Back To Top (76583)

 Hi Goran, thank you!

Monday, July 9, 2018 - 7:57:46 AM - Mohamed Back To Top (76582)

 I have a problem is how to use this subject. Please, can you give me a way to contact with you?

 

Monday, July 9, 2018 - 3:51:39 AM - Goran Back To Top (76578)

 Pablo, great job, all sumarized!














get free sql tips
agree to terms