PowerShell Script to get the Report Of Sql Services Status

There is a requirement from client that due to some ongoing issues on 10 servers client asked us to check sql services Status everyday if they are running or stopped as these servers are not yet added to Monitoring .

So, thought do this check using PowerShell that to retrieve the sql services status information. We can use Get- Service command to get required output but this works for individual server as I want to automate to retrieve information for all 10 servers in one go so, here we have used CIM commandlets to get our sql Services status report. Also we need sql Services Start Time for all Servers. The output has the fields Hostname, Name, Caption, State, ProcessID, StartName(ServiceAccount) and StartTime. Below is the PowerShell script to get the sql Services Running or Stopped State where you need to provide ComputerNames or ServerNames :

Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

From above you can see it gives all the instances installed on the Machine with Running or Stopped State, along with sql Services Start Time

You can pass Multiple ComputerNames in Script by Separating with Coma(,) like

Invoke-Command -ComputerName DESKTOP-02JIB76,Node1,Node2 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

If you want to get information of only Running or only Stopped State Services

Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'running'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName
Invoke-Command -ComputerName DESKTOP-02JIB76 {
    Get-CimInstance -ClassName Win32_Service -Filter "Name like '%sql%' and state = 'Stopped'" -PipelineVariable Service |
    Select-Object -Property SystemName, Name, Caption, State, ProcessId, StartName,
                            @{label='StartTime';expression={(Get-Process -Id $Service.ProcessId).StartTime}}
} | Format-Table -Property SystemName, Name, Caption, State, ProcessId, StartTime, StartName

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

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