Today let’s see a new way to look at Installed SQL Services on your Server from T-SQL.
Till SQL Server 2008 for verifying services status, service accounts, startup type etc..we’ve to go and verify from SQL Server Configuration manager as shown below.
Starting SQL Server 2008R2 SP1 Microsoft introduced a new DMO(sys.dm_server_services) to validate services using Simple T-SQL. Let us consider only services of “PROD” Instance in our scenario.
I’ve connected to PROD Instance and executed the below mentioned T-SQL statement.
T-SQL:
SELECT ser.servicename, ser.startup_type_desc as startup_type,
ser.status_desc as current_status, ser.last_startup_time,
ser.service_account, ser.is_clustered,
ser.cluster_nodename
FROM sys.dm_server_services ser
Output:
If you observe the result set, it is giving all the Cool information regarding installed Services. Interestingly this DMV is returning only Instance specific Services(Which makes sense because you are connected to that specific instance) such as DB Engine Service and Agent Service, not Server scoped such as SSIS. Also, it is not returning details about SSRS(PROD) and Full text search(PROD) service details!! Am not sure why 😦
You can make use of this in DENALI too. Interestingly DENALI is returning a duplicate entry for Agent Service for me!! Not sure why…i think it’ll be resolved in RTM Release of DENALI.
Happy Testing Denali!
MSSQLTREK 