Querying service information via T-SQL was introduced in SQL Server 2016 with the sys.dm_server_services Dynamic Management View. This view returns status information of services that relate to the instance that you’re querying from:
SELECT servicename, startup_type_desc, status_desc, service_account
This query has returned some basic information about the Database Engine and SQL Agent services for my localhost instance so I can see if the service is running, if the startup is manual or automatic and under which account the service is running under. I can add further columns to the query to see if the service is a clustered resource and to check if Instant File Initialisation is enabled.
Although useful if my service wasn’t running I wouldn’t be able to query anything so I would have to run services.msc (in Windows) to display all services on my machine and pull out any required information from there. Thankfully with dbatools there is an alternative; Get-DbaService which will return information on all installed services that relate to SQL Server, including the Engine, Agent, Browser as well as additional components such as Reporting Services.
Providing you have dbatools installed (if not then visit here) then from PowerShell (I’m using the integrated terminal in Visual Studio Code), run the following:
Here’s the output.
Now I have a lot of different SQL services running on my machine at moment, including three separate installs which makes the list is quite long, so to make things easier I’ll alter the command to only return services of type ‘Engine’ and also show the results in a grid view:
Get-DbaService -Type Engine | Out-GridView
Using the grid view means we can filter the result set direct so here I’m looking for service names that contain ‘SQLEXPRESS’:
Whilst I’m a big fan of using DMV’s for all kinds of tasks and having a service related view is great especially considering being able to check items like Instant File Initilisation, PowerShell really is the way to go for items such as this. The best thing is that dbatools has done all of the hard work for you (or rather the authors have) and its added flexibility means we’re not limited to local instances providing permissions are there we can query any number of remote computers as well.