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

Free SQL Server Webcast > Building Really Fast SQL Server VMs

Quick SQL Server Health Check Report

By:   |   Read Comments (11)   |   Related Tips: More > Database Administration


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.


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.


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 = "[email protected]" 
$MailTo = "[email protected]" 
$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)  
  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 
# 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 = "[email protected]"
$MailTo = "[email protected]"
$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 
DECLARE @SqlVersion varchar(200) 
SET @[email protected]@VERSION 
SELECT @@SERVERNAME as 'Server Name', 
       @SqlVersion as 'Version', 
       SERVERPROPERTY('edition') as 'Edition', 
       SERVERPROPERTY('productlevel') as 'Service Pack' 

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. 
-- cleanup temp tables in case they were left behind 
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = '#xp_readerrorlog') DROP TABLE #xp_readerrorlog 
-- declare and set variables 
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:


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

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

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, April 24, 2018 - 9:39:45 PM - Leonardo Back To Top

Thank you very much for sharing these useful scripts. 

God Bless you!




Tuesday, February 13, 2018 - 1:51:55 AM - Mathias Back To Top

Hi Gustafson,

IN case of different users and pass per instance, you should build a hash for that like this:

# SQL Server instance names
[array]$SqlServers = @("MYMACHINE\SQL2012","MYMACHINE\SQL2014")
$users = @{}
$secrets = @{}

Foreach ($SqlServer in $SqlServers)
 "-> $U, $P"
   sqlcmd -U$U -P$P -W -S $SqlServer -i $WorkDir\ErrorLogs.sql | Out-File -FilePath "$WorkDir\$OutFile" -Append



Best Regards

Monday, February 12, 2018 - 5:08:03 PM - mhickma Back To Top


If you're wanting a network acoount to run this, other than yourself, which is a SysAdmin to the SQL Server, then you would drop the -E switch and pass in the credentials using -U and -P


sqlcmd -U IADHSR3\SQLLOGON -P 5TuTT#R -W -S $SqlServer -i $WorkDir\ServerInfo.sql | Out-File -FilePath "$WorkDir\$OutFile" -Append 

Monday, February 12, 2018 - 4:28:29 PM - Joe Gavin Back To Top

Gus, You would execute this as a domain user with local Windows admin and SQL Server sysadmin rights on any boxes tou want to check. The -E sqlcmd switch picks up the AD creds. 



Monday, February 12, 2018 - 3:42:49 PM - Paul Gustafson Back To Top



Where and how do I place my login and password for each Server and SQL Server?





Monday, February 12, 2018 - 2:53:30 PM - Paul Gustafson Back To Top


 Removing the ' and backspace before the Pipe worked.


Saturday, February 10, 2018 - 7:18:56 AM - Joe Gavin Back To Top

Thanks for the feedback. I think I may need to get away from wrapping lines of text for readability as much as I do. It doesn't seem to paste well.


Saturday, February 10, 2018 - 4:31:19 AM - mathias Back To Top

Hi Hustafson,

Errors seem be due to newline before Sort-Objet, just try


@{LABEL='%utilized';EXPRESSION={"{0:N2}" -f (100 - $_.freespace/$_.capacity*100)}} | Where-Object {$_.GBcapacity -gt 20} | Sort-Object driveletter



Friday, February 09, 2018 - 5:21:32 PM - mhickma Back To Top

Remove the tick (`) and backspace from the pipe | to the prior line of syntax.

So, instead of  this -

  | Sort-Object -driveletter | Out-File -FilePath 


You should have this -

@{LABEL='%utilized';EXPRESSION={"{0:N2}" -f (100 - $_.freespace/$_.capacity*100)}} | Where-Object {$_.GBcapacity -gt 20} | Sort-Object driveletter | Out-File -FilePath "$WorkDir\$OutFile" -Append 


Friday, February 09, 2018 - 5:01:22 PM - Paul Gustafson Back To Top


I'm  getting the following error when I try to run the DbaChecks.ps1 in PowerShell:

All files are in the Temp dir as noted below.

Any ideas?





Powershell : At C:\Users\pg12345\Temp\DbaChecks.ps1:61 char:17

At line:1 char:1

+ Powershell -File "C:\Users\pg12345\Temp\DbaChecks.ps1"

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (At C:\Users\p....ps1:61 char:17:String) [], RemoteException

    + FullyQualifiedErrorId : NativeCommandError


+                 | Sort-Object -driveletter | Out-File -FilePath "$Wor ...

+                 ~

An empty pipe element is not allowed.

    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException

    + FullyQualifiedErrorId : EmptyPipeElement



Friday, February 09, 2018 - 1:20:25 PM - mhickma Back To Top


 This is great!

I had to tweak the code a bit; PS didn't like your line break during the piping, so I just backed out the tick and allowed PS to decide where to place the line break.
Also, SQL 2012 did not like the PS1 code because you have no ESCAPE_xxx for your tokens. However, it runs just fine from Run Command, and Windows Scheduler.


Learn more about SQL Server tools