PowerShell for the DBA: Is the SQL service running?

DBA, PowerShell / By

A DBA frequently needs to spot check whether a SQL service is up and running. We could fire up Management Studio and connect to that instance, sure. We could remote into that server and look at the Configuration Manager.

An image of the Windows PowerShell icon, as seen in the Windows start menu.

Why not save yourself a lot of time, and use PowerShell?

Use the PowerShell cmdlet “Get-Service”

A pre-tip tip: You can get to PowerShell from the Windows menu, but my favorite method is just CTRL-R > “PowerShell” > Enter.

Get-Service is one of the more useful cmdlets in existence. Let’s say, for example, I want to see the status of the MSSQLSERVER service – that’s the primary SQL Server engine service. That’s easy enough!

Get-Service MSSQLSERVER -ComputerName ServerName;

Note that the -Computername tag lets us look at a non-local server.

This command will return the service Status, Name, and DisplayName:

Image of a PowerShell command result, showing a MSSQLSERVER Status, Name, and DisplayName.

More features for PowerShell Get-Service

There’s all kinds of things we can do from here – PowerShell is a powerful scripting language, after all – but this is quite useful in the day-to-day.

How about checking on a handful of servers’ services? Same thing, nearly:

Get-Service MSSQLSERVER -ComputerName ServerName, OtherServer, ThirdServer;

The problem is, the results don’t specify which result belongs to which server. To fix that, we can pipe the results to either Select-Object or Format-Table, and include the “MachineName” property:

Get-Service MSSQLSERVER -ComputerName ServerName, OtherServer, ThirdServer | Format-Table MachineName, DisplayName, Status;

So, we grab the service information, pipe it to a Format-Table, and display just the machine, the service name, and its status. We’re done!

Image of a PowerShell command result with three rows, showing MachineName, DisplayName, and Status.

Want to work with us? Contact us here!