SQL Server Services Status Using DMV’s – 2008R2 SP1/Denali

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.
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,
FROM   sys.dm_server_services ser

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!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

%d bloggers like this: