Quick SQL Server Health Check Report

Problem

You have SQL Server monitoring and alerts in place, but would just like a quick health check report on your SQL Servers emailed to you daily.

Solution

We can use this PowerShell script that uses .sql scripts and a WMI call to get started.

For this solution, we have these components:

  • DbaChecks.ps1 – PowerShell code that collects the data
  • ServerInfo.sql – collects info about SQL Server
  • ErrorLogs.sql – collects data from SQL Server logs
  • Scheduled Job – a SQL Agent or Windows Task Scheduler job to run daily

Here is the code.  You will need to save the code from each section using the file names mentioned above.

DbaChecks.ps1 

<# 
script name: DbaChecks.ps1 
  
Gather info of daily interest on SQL Servers and email and attachment it. 
  
01/12/18 - original script - Joe Gavin 
#> 
  
$WorkDir = "C:\Users\jgavin\repos\dba\dbachecks - mssqltips" 
$OutFile = "DbaChecks.txt" 
   
# Edit names in arrays in alphabetical order for ordered output. Windows server names and SQL Server names are not sorted in script as edits are infrequent. 
# Windows machine names 
[array]$WinMachines = @("JGAVIN-L") 
  
# SQL Server instance names 
[array]$SqlServers = @("MYMACHINE\SQL2012"," MYMACHINE\SQL2014"," MYMACHINE\SQL2016"," MYMACHINE\SQL2017") 
  
# mail variables 
$PSEmailServer = "smtp.mymailserver.com" 
$MailFrom = "sentfrom@domain.ext" 
$MailTo = "sendto@domain.ext" 
$MailSubject = "DBA Checks" 
  
# begin 
  
Set-Location $WorkDir 
   
# test connectivity to each Win machine 
function TestConn{ 
Add-Content $WorkDir\$OutFile "Testing connectivity...`n" 
Foreach ($WinMachine in $WinMachines)  
{ 
   If (Test-Connection -Computer $WinMachine -Quiet) {Add-Content $WorkDir\$OutFile "`n$WinMachine responded`n"}  
   Else {Add-Content $WorkDir\$OutFile "`n$WinMachine not responding`n"} 
}Add-Content $WorkDir\$OutFile "`n" 
}  
  
# ServerInfo 
function ServerInfo{ 
Foreach ($SqlServer in $SqlServers) 
{ 
   sqlcmd -E -W -S $SqlServer -i $WorkDir\ServerInfo.sql | Out-File -FilePath "$WorkDir\$OutFile" -Append 
}} 
  
# ErrorLogs 
function ErrorLogs{ 
Foreach ($SqlServer in $SqlServers) 
{ 
   sqlcmd -E -W -S $SqlServer -i $WorkDir\ErrorLogs.sql | Out-File -FilePath "$WorkDir\$OutFile" -Append 
}} 
  
# DiskSpace 
function DiskSpace{ 
Foreach ($WinMachine in $WinMachines)  
{  
  $error.clear() 
  Get-WmiObject -Class win32_volume -cn $WinMachine -filter "DriveType=3" | Select-Object @{LABEL='Machine';EXPRESSION={$WinMachine}},driveletter, @{LABEL='GBcapacity';EXPRESSION={"{0:N1}" -f ($_.capacity/1GB)}}, @{LABEL='%utilized';EXPRESSION={"{0:N2}" -f (100 - $_.freespace/$_.capacity*100)}} | Where-Object {$_.GBcapacity -gt 20} | Sort-Object driveletter | Out-File -FilePath "$WorkDir\$OutFile" -Append    
  $error | Out-File -FilePath "$WorkDir\$OutFile" -Append # write any PowerShell errors to out file 
  $error.clear()     # clear variable so it can be used in other functions 
}} 
   
# delete old out file 
If (Test-Path "$WorkDir\$OutFile"){Remove-Item "$WorkDir\$OutFile"} 
  
# datestamp 
$HostName = hostname 
Write-Output "Report run from $HostName started $(get-date)  `r`n" | Out-File -FilePath "$WorkDir\$OutFile" -Append 
  
# call functions or comment out to not run 
TestConn 
ServerInfo 
ErrorLogs 
DiskSpace 
  
# email report file 
Send-MailMessage -From $MailFrom -To $MailTo -Subject $MailSubject -Attachments $OutFile

Just to break it down the above code a bit, set your variables in this section:

$WorkDir = "C:\Tools\DbaChecks"
$OutFile = "DbaChecks.txt" 
      
# Edit names in arrays in alphabetical order for ordered output. 
# Windows machine names
[array]$WinMachines = @("JGAVIN-L")
# SQL Server instance names
[array]$SqlServers = @("JGAVIN-L\SQL2012","JGAVIN-L\SQL2014","JGAVIN-L\SQL2016","JGAVIN-L\SQL2017")
# mail variables
$PSEmailServer = "smtp.cabotcheese.com"
$MailFrom = "test@agrimark.net"
$MailTo = "jgavin@agrimark.net"
$MailSubject = "DBA Checks"

The rest of the script creates functions to ping each Windows machine, gather each server’s version info, reads the error log and gathers disk space size and utilization percentages. The functions are then each called and output written to an output file and the file is sent as an email attachment.

Here’s a quick look at each of the SQL scripts that are called.

ServerInfo.sql - displays system variables and SERVERPROPERTY arguments to show name, version, edition and Service Pack level.

 /* script name: ServerInfo.sql 
   gathers sql server version and edition 
*/ 
  
SET NOCOUNT ON 
  
DECLARE @SqlVersion varchar(200) 
SET @SqlVersion=@@VERSION 
  
SELECT @@SERVERNAME as 'Server Name', 
       @SqlVersion as 'Version', 
       SERVERPROPERTY('edition') as 'Edition', 
       SERVERPROPERTY('productlevel') as 'Service Pack' 
PRINT '' 
GO

ErrorLogs.sql - Reads the error log for each for the last 24 hours (36 if it’s a Monday).

 /* 
script name: ErrorLogs.sql 
Runs xp_readerrorlog to query the errorlog for entries for the past x amount of days. 
*/ 
  
SET NOCOUNT ON 
  
-- cleanup temp tables in case they were left behind 
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = '#servername') DROP TABLE #servername 
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = '#xp_readerrorlog') DROP TABLE #xp_readerrorlog 
  
-- declare and set variables 
DECLARE @NumOfLogDays INT 
DECLARE @startdate DATETIME 
DECLARE @enddate DATETIME 
  
IF (SELECT DATENAME(WEEKDAY, GETDATE())) like 'Monday' SET @NumOfLogDays = 3 ELSE SET @NumOfLogDays = 1 -- if it's Monday get 3 days of jobs 
SET @startdate=GETDATE() - @NumOfLogDays 
SET @enddate=GETDATE() 
  
-- create and populate temp tables 
CREATE TABLE #servername (ServerName VARCHAR(100)) 
INSERT INTO #servername 
SELECT @@servername 
  
CREATE TABLE #xp_readerrorlog(LogDate varchar(30),ProcessInfo varchar(30),Text varchar(max)) 
INSERT INTO #xp_readerrorlog 
EXEC xp_readerrorlog 0,1,NULL,NULL,@startdate,@enddate,'asc' 
  
-- join temp tables 
SELECT a.ServerName, b.LogDate, b.Text as 'Text ' 
FROM #servername a, #xp_readerrorlog b 
  
-- add whitespace 
PRINT ' ' 
  
-- cleanup 
DROP TABLE #servername 
DROP TABLE #xp_readerrorlog

Output from Scripts

This produces a file that looks like this (edited for readability) attached to an email:

notepad

To Setup

  1. Copy the code into DbaChecks.ps1, ErrorLogs.sql and ServerInfo.sql and save to a directory
  2. Configure your variables in DbaChecks.ps1
  3. Schedule in SQL Agent or Windows Task Scheduler

That’s it.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *