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

 

Quick SQL Server Health Check Report


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

ALERT: Did you know 66% of DBAs say their workload is increasing! - Click here to learn more.


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 = "[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)  
{  
  $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 = "[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 
*/ 
  
SET NOCOUNT ON 
  
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' 
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


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!

 

Leonardo 

 


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 = @{}
$users.add("MYMACHINE\SQL2012","user1")
$secrets.add("MYMACHINE\SQL2012","secret1")
$users.add("MYMACHINE\SQL2014","user2")
$secrets.add("MYMACHINE\SQL2014","secret2")

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

 

Mathias

Best Regards


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

 Gus, 

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

Example

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?

 

Thanks,

 

Gus


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

 

 Removing the ' and backspace before the Pipe worked.

Thanks!


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

 

Regards


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

 Gus,
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

Hi,

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?

Thanks,

Gus

--------------------------------------------------------------------------------------------------

Temp\DbaChecks.ps1"

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