master Service Facts

Overview

Everything on this page is the kind of fact somebody currently gets by opening a remote desktop session to the server and looking at the Configuration Manager:

  • the service account
  • the start mode
  • the error log path
  • the port it is really listening on
  • whether this is a cluster node

All of it has been queryable since SQL Server 2008 R2 SP1 and nothing in this product has ever asked.

The master Service Facts page: the service account, the ports and the dumps
The whole page. Running since 8 September with no memory dumps, the axis carrying the service start, and the grid holding the facts somebody otherwise collects over a remote desktop session.

Where to find it

Expand a server in the tree, expand the master database, then Master → Service Facts.

Shown on SQL Server 2012 and newer.

The bottom half is the part nobody looks at

sys.dm_server_memory_dumps lists every minidump this instance has written, with a date and a size.

An instance that has written four dumps, three of them since the last patch, is telling somebody something, and today the only way to find out is to notice the folder.

The dumps are drawn on a time axis, so a cluster of them after a patch reads as a cluster.

One thing about permissions

All three views need VIEW SERVER STATE, and all three answer with an empty set rather than an error when a login does not have it.

For a page whose whole point is completeness, that is the worst possible failure, so the caveat line says so whenever the page comes back short.

Reading the chart

The axis: service starts and memory dumps together
One tick, which is the last service start. Any dump this instance had written would sit on the same axis beside it.

Service starts and memory dumps share one axis, so a run of dumps after a patch reads as a run rather than as five rows in a list.

An instance with no dumps draws the starts on their own. That is what a healthy one looks like, and it is also what a login without VIEW SERVER STATE sees, which is why the caveat line says which of the two this is.

Reading the grid

The grid: one fact per row with what it means
The service account, the start mode, the ports, the paths and the build, each with a sentence saying what that value implies or what to check next.
Column What it is
Fact The property.
Value What this instance has.
What it means One sentence: what this value implies, or what to check next.

The facts include the service name and account, the start mode, whether the service is running under a managed service account, the process id, the last startup time, the error log path, the listening port and IP configuration, the cluster node name where there is one, and the instance’s build and edition.

The toolbar

Control What it does
Startup procedures Opens master Startup Procedures, which shares the registry read.
Error log Opens the Error Log report, at the path this page names.
Copy the fact sheet Copies everything on the page as text, for a runbook or a ticket.

Where the data comes from

Source What it gives
sys.dm_server_services The service name, account, start mode, status, process id and last startup.
sys.dm_server_registry The error log path, the listening port and IP configuration, the startup parameters.
sys.dm_server_memory_dumps Every minidump, with its path, date and size.
SERVERPROPERTY Build, edition, cluster node name, and whether the instance is clustered.
Report Why you would go there
master Startup Procedures The startup parameters, including trace flags set with -T.
Error Log What the instance wrote around the time of a dump.
master Backup and Rebuild Readiness The build, which a restore of master has to match.
Trace Flags Which flags are on now, and which survive a restart.
Failover Compatibility The build and edition across the estate.

Frequently asked questions

The page shows fewer facts than I expected. This login does not have VIEW SERVER STATE. These views return an empty set rather than an error, so the caveat line is the only warning there is.

There are minidumps. Is the instance unhealthy? Not necessarily, and the page does not say so. What it says is that they exist, how many, and when. A cluster of them after a patch is worth a support case; one from 2018 is history.

What account does the service run as? On this page, from sys.dm_server_services, without opening a remote desktop session.

The port here is different from the one in my connection string. Then something else is in the path: a listener, an alias, or a load balancer. The port on this page is what the instance is actually listening on.