Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Comprehensive SQL Server Monitoring Report via Email


By:   |   Read Comments (9)   |   Related Tips: More > 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 ([email protected])/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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. Last year I switched jobs to a DBA position, where I've been suited to implement new processes and optimize existing ones.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

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

 

 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 09, 2018 - 6:19:44 PM - Pablo Echeverria Back To Top

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


Monday, July 09, 2018 - 8:56:32 AM - Pablo Echeverria Back To Top

 Hi Goran, thank you!


Monday, July 09, 2018 - 7:57:46 AM - Mohamed Back To Top

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

 


Monday, July 09, 2018 - 3:51:39 AM - Goran Back To Top

 Pablo, great job, all sumarized!


Learn more about SQL Server tools