sys.dm_server_services (Transact-SQL)

 

Updated: November 16, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about the SQL Server, Full-Text, and SQL Server Agent services in the current instance of SQL Server. Use this dynamic management view to report status information about these services.

Column nameData typeDescription
servicenamenvarchar(256)Name of the SQL Server, Full-text, or SQL Server Agent service. Cannot be null.
startup_typeintIndicates the start mode of the service. The following are the possible values and their corresponding descriptions.

0: Other
1: Other
2: Automatic
3: Manual
4: Disabled

Is nullable.
startup_descnvarchar(256)Describes the start mode of the service. The following are the possible values and their corresponding descriptions.

Other: Other (boot start)
Other: Other (system start)
Automatic: Auto start
Manual: Demand start
Disabled: Disabled

Cannot be null.
statusintIndicates the current status of the service. The following are the possible values and their corresponding descriptions.

1: Stopped
2: Other (start pending)
3: Other (stop pending)
4: Running
5: Other (continue pending)
6: Other (pause pending)
7: Paused

Is nullable.
status_descnvarchar(256)Describes the current status of the service. The following are the possible values and their corresponding descriptions.

Stopped: The service is stopped.
Other(start operation pending): The service is in the process of starting.
Other (stop operation pending): The service is in the process of stopping.
Running: The service is running.
Other (continue operations pending): The service is in a pending state.
Other (pause pending): The service is in the process of pausing.
Paused: The service is paused.

Cannot be null.
process_idintThe process ID of the service. Cannot be null.
last_startup_timedatetimeoffset(7)The date and time the service was last started. Is nullable.
service_accountnvarchar(256)The account authorized to control the service. This account can start or stop the service, or modify service properties. Cannot be null.
filenamenvarchar(256)The path and filename of the service executable. Cannot be null.
is_clusterednvarchar(1)Indicates whether the service is installed as a resource of a clustered server. Cannot be null.
cluster_nodenamenvarchar(256)The name of the cluster node on which the service is installed. Is nullable.
instant_file_initialization_enablednvarchar(256)Applies to: Starting SQL Server 2016 SP1.

Specifies whether instant file initialization is enabled for SQL Server Database Engine service. This property does not apply to services (example: SQL Server Agent) other than SQL Server Database Engine service. nullable.

Y = instant file initialization is enabled for the service.

N = instant file initialization is disabled for the service.

Null = doesn’t apply to service.

Permissions

Requires VIEW SERVER STATE permission on the server.

sys.dm_server_registry (Transact-SQL)

Community Additions

ADD
Show: