The Multifunctioning DBA

Jun 25 2009   9:24PM GMT

PowerShell SQL Server Health Check Script



Posted by: Colin Smith
Tags:
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

14  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Abcdefrgh
    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"
    0 pointsBadges:
    report
  • VVNN
    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
    0 pointsBadges:
    report
  • JoMaitland
    "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.
    1,820 pointsBadges:
    report
  • JoMaitland
    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.
    1,820 pointsBadges:
    report
  • Dyard12
    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?
    0 pointsBadges:
    report
  • Colin Smith
    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
    885 pointsBadges:
    report
  • Scuby
    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.
    0 pointsBadges:
    report
  • Colin Smith
    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.
    885 pointsBadges:
    report
  • Senmayi
    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
    0 pointsBadges:
    report
  • Colin Smith
    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.
    885 pointsBadges:
    report
  • DanB26
    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
    0 pointsBadges:
    report
  • Colin Smith
    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
    885 pointsBadges:
    report
  • Jackandjill
    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
    0 pointsBadges:
    report
  • Colin Smith
    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.
    885 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: