I recently found this DMV and couldn’t wait to share.
sys.dm_server_services
What’s so great about it? For each service for this instance (SQL Server Database Engine, Full-text, and SQL Server Agent services)
- Startup type. (Automatic, manual etc)
- Service account.
- Status (Is the Agent up? I mean the DB Engine better be or how are you seeing this anyway?)
- Last startup time (other places you can get this but still very convinent).
- Did I mention the service account?
- Is instant file initialization enabled?
Yes, you can find this information elsewhere but this is exceptionally handy, particularly when someone says “What service account is instance XYZ running on?”
Oh, and just FYI there is also a sys.dm_server_registry that has the Windows registry settings for SQL Server. Need to know all of the IP addresses? Ports? Are they dynamic?
I’ve been working with a number of servers on Windows Core and so haven’t had access to the Configuration Manager directly and I’ve had security issues using it remotely so let me tell you, for me at least, these DMVs are a huge help.