New DMV in SQL Server 2012 sys.dm_server_services
One of the new Dynamic Management Views introduced in SQL Server 2012 is the dm_server_services which returns info on the services running that are associated with SQL Server.
select * from sys.dm_server_services;
Give it a try.
Looking this over, the first thing that comes to mind is to to use this to be able to detect if the SQL Server Agent Process is running or even if it is set to automatic startup.
One thing that you will notice is that the last_startup_time will always be null for SQL Server Agent. To bad they only got this one partially working before it was released. I expect we will see that change on a future service pack.
Where is SQL Server Installed?
I don’t know how many times I have started working on a new SQL Server and had to first figure which of the several installed locations for SQL Server is actually being used. Here is an example of how to find out which directory install of SQL server is being used using the sys.dm_server_services DMV.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Nice blog. I tried the new DMV and I have a value for the agent if it is running. When it is stopped, the process_id and startup time are null. The same thing goes for the full text services. That might be a good thing as you can check the servicename for the agent and last startup time for null to see if it is running or check the status_desc or status number value.
Maybe by design from MS.
Good point, checking for null for process_id will work, or status_desc, although last_startup_time appears to always be null for the agent.