Home > IT Blogs > The Multifunctioning DBA > PowerShell SQL Server Health Check Script
>>VIEW ALL POSTS  

The Multifunctioning DBA

«
»
Jun 25 2009   9:24PM GMT

PowerShell SQL Server Health Check Script



Posted by: Colin Smith
Excel, Monitoring, MSSQL, Powershell, SQL Server

OK so I think that I have everything I want in my script to check on the health of my SQL Servers. I will run this at least once a day and more if needed. I hope that this will help some people out and if you have any questions, comments, or ideas of what else might be good to include please let me know.

SQL Health Check Script:





#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
#$Excel.visible = $False

#$workbook = $excel.Workbooks.Open("D:\pac health checks.xlsx")

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 2

#Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv "\\pni-vmdbasql\d$\monitoring\inputs\sqltab.txt"
$servers = Import-Csv "\\ent-pocpacapcx01\d$\monitoring\sqltab.txt"

#########################################################
foreach ($entry in $servers)
{
$torp = $entry.TorP
$mon = $entry.monitor
$machine = $entry.server
$errorlog = $entry.errorlog
$os = $entry.os2000
$iname = $entry.Instance
if ($iname -eq "Null")
{
$instance = "$machine"
}
else
{
$instance = "$machine\$iname"
}
if ($torp -eq "Prod")
{
$ServerType = "Production"
}
else
{
$ServerType = "Test"
}
$instance = $instance.toupper()

#########################################################
#foreach ($instance in get-content "D:\SQL_Servers.txt")
#{

#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
################################################

#This script gets SQL Server database information using PowerShell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Create an SMO connection to the instance
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases

#$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

#Formatting using Excel

$version = $s.VersionString

if ($version -like "*9.00.1399*")
{        $sqlversion = "SQL Server 2005 No Service Pack"
}
elseif ($version -like "*9.00.2047*")
{        $sqlversion = "SQL Server 2005 SP1"
}
elseif ($version -like "*9.00.3042*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3068*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3054*")
{        $sqlversion = "SQL Server 2005 SP2 with Q934458"
}
elseif ($version -like "*9.00.3073*")
{        $sqlversion = "SQL Server 2005 SP2 with Q954606"
}
elseif ($version -like "*9.00.3186*")
{        $sqlversion = "SQL Server 2005 SP2 with Q939562"
}
elseif ($version -like "*9.00.4035*")
{        $sqlversion = "SQL Server 2005 SP3"
}
## SQL Server 2000 Checks
elseif ($version -like "*8.00.194*")
{        $sqlversion = "SQL Server 2000 No SP"
}
elseif ($version -like "*8.00.384*")
{        $sqlversion = "SQL Server 2000 SP1"
}
elseif ($version -like "*8.00.532*")
{        $sqlversion = "SQL Server 2000 SP2"
}
elseif ($version -like "*8.00.760*")
{        $sqlversion = "SQL Server 2000 SP3"
}
elseif ($version -like "*8.00.818*")
{        $sqlversion = "SQL Server 2000 SP3 with MS03-031"
}
elseif ($version -like "*8.00.2039*")
{        $sqlversion = "SQL Server 2000 SP4"
}
elseif ($version -like "*8.00.2040*")
{        $sqlversion = "SQL Server 2000 NoSP with Q274329"
}
elseif ($version -like "*8.00.2050*")
{        $sqlversion = "SQL Server 2000 SP4 with MS08-040"
}
elseif ($version -like "*8.00.2187*")
{        $sqlversion = "SQL Server 2000 SP1 with Q923849"
}
elseif ($version -like "*8.00.2282*")
{        $sqlversion = "SQL Server 2000 QFE"
}
elseif ($version -like "*8.00.3073*")
{        $sqlversion = "SQL Server 2000 SP2 with Q954606"
}
#SQL Server 7
elseif ($version -like "*7.00.1094*")
{        $sqlversion = "SQL Server 7 SP4 with Q815495, Q821279"
}
#SQL Server 2008
elseif ($version -like "*10.0.1442.32*")
{        $sqlversion = "SQL Server 2008 X64 MSDN Beta"
}

else
{        $sqlversion = "Check me $version"
}

$Sheet.Cells.Item($intRow,3) = "Version:"
$Sheet.Cells.Item($intRow,4) = $sqlversion
$Sheet.Cells.Item($intRow,5) = $ServerType
if ($Servertype -eq "Production")
{
$fgColor = 35
}
else
{
$fgColor = 0
}
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

###########################################################

$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True
$Sheet.Cells.Item($intRow,4).Font.Bold = $True
$Sheet.Cells.Item($intRow,5).Font.Bold = $True

##############################################
## Are full and tranlog backups running?
## DB Reindexing running?
##############################################

$intRow ++

$jobsserver = $s.JobServer
$jobs = $jobsserver.Jobs
$ijob = 0
$backjob = 0
$tranjob = 0
foreach ($job in $jobs)
{

if (($job.name -like "*backup*") -and ($job.name -notlike "*Tranlog*"))
{
$backjob = 1
$fullbackup = $job.name
$fullbackupstatus = $job.LastRunOutcome
$fullbackupdate = $job.LastRunDate

}
if ($job.Name -like "*tranlog*")
{
$tranjob = 1
$tranlogbackup = $job.name
$tranlogbackupstatus = $job.LastRunOutcome
$tranlogbackupdate = $job.LastRunDate

}
if ($job.Name -like "*index*")
{
$ijob = 1
$rebuildindex = $job.Name
$rebuildindexstatus = $job.LastRunOutcome
$rebuildindexdate = $job.LastRunDate

}
}

## Check to verify full backups are running ##
$dayago = [datetime]::Now.AddDays(-1)
if (($fullbackupstatus -eq "Succeeded") -and ($fullbackupdate -gt $dayago))
{
$fullbackupjob = "Full Backups Running"
}
elseif(($fullbackupstatus -ne "succeeded") -or ($fullbackupdate -lt $dayago))
{
$fullbackupjob = "Full Backups Failing"
}
if ($backjob -eq 0)
{
$fullbackupjob = "No Full Backup Job"
}

## Check to verify that Tranlogs backups are running fine ##
$hourago = [datetime]::Now.AddHours(-1)
if (($tranlogbackupstatus -eq "Succeeded") -and ($tranlogbackupdate -gt $hourago))
{
$tranbackupjob = "Tran Backup Running"

}
if(($tranlogbackupstatus -ne "succeeded") -or ($tranlogbackupdate -lt $hourago))
{
$tranbackupjob = "Tran BackupFailing"
}
if ($tranjob -eq 0)
{
$tranbackupjob = "No Tran Backup Job"
}

$weekago = [datetime]::Now.AddDays(-7)
if (($rebuildindexstatus -eq "Succeeded") -and ($rebuildindexdate -gt $weekago))
{
$indexjob = "Indexing Running"

}
elseif(($rebuildindexstatus -ne "succeeded") -or ($rebuildindexdate -lt $weekago))
{
$indexjob = "Indexing Failing"

}
if ($ijob -eq 0)
{
$indexjob = "No Indexing Job"

}

$Sheet.Cells.Item($intRow,1) = $fullbackupjob
$Sheet.Cells.Item($intRow,2) = $tranbackupjob
$Sheet.Cells.Item($intRow,3) = $indexjob
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True

##############################################

$intRow++

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,3) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,4) = "SPACE AVAILABLE (MB)"
$Sheet.Cells.Item($intRow,5) = "DATA DRIVE"
$Sheet.Cells.Item($intRow,6) = "SPACE AVAILABLE ON DISK (GB)"
$Sheet.Cells.Item($intRow,7) = "MIRROR STATUS"
$Sheet.Cells.Item($intRow,8) = "LOG SIZE (MB)"

#Format the column headers
for ($col = 1; $col –le 8; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

$intRow++
#######################################################
#      #This script gets SQL Server database information using PowerShell
#
#      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#
#      # Create an SMO connection to the instance
#      $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
#
#      $dbs = $s.Databases
#
#      #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
#
#      #Formatting using Excel

foreach ($db in $dbs)
{
$name = $db.name
$model = $db.recoverymodel
if ($model -eq 1)
{
$modelname = "Full"
}
elseif ($model -eq 2)
{
$modelname = "Bulk Logged"
}
elseif ($model -eq 3)
{
$modelname = "Simple"
}

$logfiles = $db.LogFiles
foreach ($log in $logfiles)
{
$logsize = $log.size/1KB
$logsize = [math]::Round($logsize, 2)
}

#if(($name -ne "master") -and ($name -ne "model")) # -and ($name -ne "msdb"))
#{
#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB

#Format the results to a number with three decimal places
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

$Sheet.Cells.Item($intRow, 1) = $db.Name

$Sheet.Cells.Item($intRow, 2) = $modelname
$Sheet.Cells.Item($intRow, 3) = "{0:N3}" -f $db.Size

#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -eq 0.00)
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow, 4) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor

$dblocation = $db.primaryfilepath

$dblocation = $dblocation.split(":")

$dbdrive = $dblocation[0]
$drives = Get-WmiObject -ComputerName $machine Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
foreach($drive in $drives)
{
$size1 = $drive.size / 1GB
$size = "{0:N2}" -f $size1
$free1 = $drive.freespace / 1GB
$free = "{0:N2}" -f $free1
$ID = $drive.DeviceID
$a = $free1 / $size1 * 100
$b = "{0:N2}" -f $a

if ($dbdrive -eq "C")
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow,5) = $dbdrive
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

if ($id -like "$dbdrive*")
{

if ($free1 -lt 5)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
if (($ID -eq "C:") -and ($free1 -lt 1))
{
$fgColor = 38
}
$Sheet.Cells.Item($intRow,6) = $free1
$Sheet.Cells.item($intRow, 6).Interior.ColorIndex = $fgColor
}
}
if($version -like "*2000*")
{$mirrorstate = 0}
else
{
$mirrorstate = $db.MirroringStatus
}
if ($mirrorstate -eq 0)
{
$mirror = "No Mirror"
}
if ($mirrorstate -eq 1)
{$mirror = "Suspended"
}
if($mirrorstate -eq 5)
{
$mirror = "Synchronized"
}
if ($mirrorstate -eq 1)
{
$fgcolor = 38
}
else
{
$fgcolor = 0
}
$Sheet.Cells.Item($intRow,7) = $mirror
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

if ($logsize -gt 500)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow,8) = $logsize
$Sheet.Cells.item($intRow, 8).Interior.ColorIndex = $fgColor

$intRow ++

#}
}

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

$workbook.saveas("D:\pac health checks.xlsx")
#$excel.Quit()
$WorkBook = $Null
$WorkSheet = $Null
$Excel = $Null
[GC]::Collect()

Here is a sample of what the report will look like when you have run it.

Report Output Sample

Report Output Sample

  Bookmark and Share     Comment     RSS Feed     Email a friend

Comment on this Post

Leave a comment:

Abcdefrgh  |   Aug 26, 2009  6:31 PM (GMT)

should we make anychanges for this do and what are the fields i need to provide at this columns: #Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv “\pni-vmdbasqld$monitoringinputssqltab.txt”
$servers = Import-Csv “\ent-pocpacapcx01d$monitoringsqltab.txt”


 

VVNN  |   Aug 27, 2009  3:34 PM (GMT)

Hi Colin,

I have some questions regarding the ps script:
1- Could you please tell me how file “monitoringsqltab.txt” look like?
2- What does this stmt mean:
$servers = Import-Csv “ent-pocpacapcx01d$monitoringsqltab.txt”
3- How different of these two input files “monitoringsqltab.txt” and “D:SQL_Servers.txt”
4- On the output file, can you display the SQL version with EE/Standard/Developer edition? Is there any reference to do so?

Thanks,
-VN


 

Colin Smith  |   Oct 20, 2009  4:36 PM (GMT)

“monitor”,”Server”,”Instance”,”TorP”,”ErrorLog”,”Ping” are the headers in the sqltab file. Monitor is ‘Y’ or ‘N’, Server is the physical host name, Instance is the instance name and null if default instance, TorP is T for test and P for Prod, Errorlog is the location of the errorlog, and Ping is if I am running my ping servers script against this server.


 

Colin Smith  |   Oct 20, 2009  4:37 PM (GMT)

You should make changes to reflect the paths to your input file. The top line and the bottom line are the same thing just reflecting two paths that I can change between for different server lists.


 

Dyard12  |   Jul 27, 2011  11:42 PM (GMT)

I am trying to get this to work and I am a new DBA, Can someone tell me what sections I need to modify? I see the path but for the import csv do I need to create those?


 

Smithc32  |   Jul 29, 2011  2:58 PM (GMT)

Dyard,

can you hit me up on twitter and I will be happy to help you out with what you are having issue with. twitter.com/smithc032 or @smithco32


 

Scuby  |   Sep 14, 2011  8:41 AM (GMT)

I found your Powershell SQL Health Script, but am having trouble getting it to run. ($servers seems to remain a null value after reading in the txt file.)
Please help.


 

Smithc32  |   Sep 18, 2011  11:39 PM (GMT)

Scuby, I would love to help you out. Do you have the servers file in the correct location and do you have it formatted correctly? If you do and you need some help please let me know. Send me a message on Twitter @smithc032 and I will help you out.


 

Senmayi  |   Oct 21, 2011  12:33 PM (GMT)

Hi Smith,
I have some doubt
1. sqltab.txt file should be stored in which location
2. please can u share the content of the sqltab.txt file
Thanks
mayi


 

Smithc32  |   Oct 27, 2011  11:02 PM (GMT)

See my newest post on the 30th of October and you will see some more information on the input file, how it is set up, and how it is used. I am going to be doing a series of posts on this script and some other options you have for the script. Hope it helps and if you still have questions let me know.


 

DanB26  |   Oct 31, 2011  3:11 PM (GMT)

Hi Colin –
I have a few questions as well…. Im fairly new to PS. I plugged in the values for my environment (input file path). The file is a simple txt file with only one entry. Does it need to be fully qualified? I simply put “servername1″ and saved it.
In looking at the script with powerGUI, it shows that it cant a) read the server, or b) get the instance. The excel book comes up, but the instance is just a “”…. none of the values seem to be populating.
Could you lend me some help please?
Thanks
Dan


 

Smithc32  |   Nov 2, 2011  4:37 PM (GMT)

DANB26 – Check out my latest post that dives into the input file a bit more and I think will answer your questions about how the script is reading the file and what it expects to see in the file.
http://itknowledgeexchange.techtarget.com/dba/powershell-sql-server-health-check-script-revisited/

Hope that will help and if you need more help let me know by commenting on the newer post.

Thanks


 

Jackandjill  |   Apr 20, 2012  1:55 AM (GMT)

Hi i am having trouble getting the code to work please, let me know how to pass username and password so that i wont useraccess restricted error


 

smithc32  |   Sep 4, 2012  4:52 PM (GMT)

Jackandjill,
The script is assuming that you are using Windows Authentication to connect to SQL Server. Do you not have the permissions required to do so? Also for the drive sizes portion of the script, it is connecting to WMI also using your windows credentials. You need to make sure that you have the appropriate rights on your servers to make that connection as well as make sure that you do not have firewall restrictions that are blocking that connection to WMI.
Hope that helps.