Checking SQL Services with PowerShell

Checking SQL Services with PowerShell

From time to time every SQL DBA needs to know if their SQL Database servers are functioning properly. With PowerShell's Get-Service command-let it is

SQL Counters with PowerShell
Reading and Writing to SQL Databases with PowerShell

From time to time every SQL DBA needs to know if their SQL Database servers are functioning properly. With PowerShell’s Get-Service command-let it is easy.

$servers ="server01, server02, server03" # Creates an array of SQL servers
$servicestatus = @{} # Creates a blank array
foreach ($server in $servers)
{
Get-Service -name *sql* -Computername $server -ErrorAction SilentlyContinue |
Sort-Object -Property DisplayName |
foreach{
$k = $server + " -" + $_.DisplayName
$v = $_.Status
$servicestatus[$k] = $v
Get-Service $_.Name |
Select-Object -ExpandProperty ServicesDependedOn |
foreach {
$kd = $k + " has dependency on " + $_.DisplayName
$s = $_.Status
$serviceStatus[$kd] = $s
}}}

#See all Results
$servicestatus | Format-Table -AutoSize

#See Running Status of each service
$servicestatus.GetEnumerator() |
Sort-Object Value |
Format-Table -AutoSize

#Show only stopped services
$servicestatus.GetEnumerator() |
where-object{$_.Value -eq "Stopped"} |
Format-Table -AutoSize

If you wanted you could incorporate the Send-SimpleMail function found here https://www.thecodeasylum.com/send-mail-with-powershell/

$body = $servicestatus.GetEnumerator() |
where-object{$_.Value -eq "Stopped" |
Format-Table -Autosize | Out-String
Send-SimpleMail -subject "Stopped Services" -body $body
 

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of