Quick SQL Server Health Check Report

By:   |   Comments (13)   |   Related: 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 @SqlVersion=@@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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

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

View all my tips

Comments For This Article

Thursday, May 9, 2019 - 7:23:12 AM - Joe Gavin Back To Top (80002)

Atallah, could you paste what it looks like into a comment?

Wednesday, May 8, 2019 - 3:54:36 PM - Atallah Back To Top (79993)

Thanks for sharing such useful script, but i am getting outfile (DbaChecks.txt) as junk not in readable format?

Tuesday, April 24, 2018 - 9:39:45 PM - Leonardo Back To Top (75776)

Thank you very much for sharing these useful scripts. 

God Bless you!




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

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 (75192)


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 (75191)

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 (75190)



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 (75189)


 Removing the ' and backspace before the Pipe worked.


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

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 (75173)

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 9, 2018 - 5:21:32 PM - mhickma Back To Top (75168)

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 9, 2018 - 5:01:22 PM - Paul Gustafson Back To Top (75167)


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 9, 2018 - 1:20:25 PM - mhickma Back To Top (75163)


 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.


get free sql tips
agree to terms