"... with no RDP access, how can I tell if the SQL Server Agent service is running?"
"... and I don't think CDC is working. Is the SQL Agent on?"
"... how can I tell how long has the SQL Server been running? Did somebody reboot it last night?"
"... so hey I need to know if the SQL Server service set to automatic startup."
Here's an easy script that anyone can use, it can be especially helpful for developers without RDP access to the Windows Server that is hosting the SQL Server service. It'll answer these developer questions and more, in addition to a lot of other utility for you as a DBA.
SELECT servicename -- Ex: SQL Server (SQL2K8R2) , startup_type_desc -- Manual, Automatic , status_desc -- Running, Stopped, etc. , process_id , last_startup_time -- datetime , service_account , filename , is_clustered -- Y/N , cluster_nodename FROM sys.dm_server_services
You don't need to be a member of the sysadmin server role to run this, but you do need VIEW SERVER STATE. Developers can be given this permission, even in production, as it gives them access to many key dynamic management views (DMVs), including DMV's for diagnostics and performance tuning.
MSDN reference: https://technet.microsoft.com/en-us/library/Hh204542(v=SQL.110).aspx
1 comment:
It doesn't report is_clustered and cluster_nodename correctly. Many people reporting this. Even a Connect article closed with a status of Won't fix: https://connect.microsoft.com/SQLServer/feedback/details/810483/sys-dm-server-services-not-reporting-cluster-data-when-fci-clustered-on-windows-server-2012-r2
Also, issues with SQLAgent details appearing as null: https://connect.microsoft.com/SQLServer/feedback/details/664557/sys-dm-server-services-last-startup-time-is-always-null-for-sql-agent
Nice DMV but looks like Microsoft gave up supporting it.
Post a Comment