DMV with SQL Server Service info (including the service accounts)

5

September 12, 2019 by Kenneth Fisher

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.

5 thoughts on “DMV with SQL Server Service info (including the service accounts)

  1. dallasbikr says:

    Just found that myself a couple of weeks ago. Beats the badly written PowerShell script i was using!

  2. Since which version does this exist? its not on the document

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 )

Google photo

You are commenting using your Google 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.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,085 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: