Skip to content

Latest commit

 

History

History
49 lines (41 loc) · 4.56 KB

sys-dm-server-services-transact-sql.md

File metadata and controls

49 lines (41 loc) · 4.56 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sys.dm_server_services (Transact-SQL)
sys.dm_server_services returns information about the services in the current SQL Server instance.
rwestMSFT
randolphwest
02/09/2024
sql
system-objects
reference
dm_server_services
sys.dm_server_services
sys.dm_server_services_TSQL
dm_server_services_TSQL
sys.dm_server_services dynamic management view
TSQL

sys.dm_server_services (Transact-SQL)

[!INCLUDE SQL Server]

Returns information about the [!INCLUDE ssnoversion-md], Full-Text, SQL Server Launchpad service ([!INCLUDE sssql17-md] and later versions), and SQL Server Agent services in the current instance of [!INCLUDE ssNoVersion]. Use this dynamic management view to report status information about these services.

Column name Data type Description
servicename nvarchar(256) Name of the [!INCLUDE ssDEnoversion], Full-text, or SQL Server Agent service.

Not nullable.
startup_type int Indicates 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

Nullable.
startup_type_desc nvarchar(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

Not nullable.
status int Indicates 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

Nullable.
status_desc nvarchar(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.

Not nullable.
process_id int The process ID of the service.

Not nullable.
last_startup_time datetimeoffset(7) The date and time the service was last started. Nullable.
service_account nvarchar(256) The account authorized to control the service. This account can start or stop the service, or modify service properties.

Not nullable.
filename nvarchar(256) The path and filename of the service executable.

Not nullable.
is_clustered nvarchar(1) Indicates whether the service is installed as a resource of a clustered server.

Not nullable.
cluster_nodename nvarchar(256) The name of the cluster node on which the service is installed. Nullable.
instant_file_initialization_enabled nvarchar(1) Specifies whether instant file initialization is enabled for the [!INCLUDE ssDEnoversion] service.

Y = instant file initialization is enabled for the service.
N = instant file initialization is disabled for the service.

Nullable.

Note: This option doesn't apply to other services such as the SQL Server Agent.

Applies to: [!INCLUDE sssql11] SP 4, [!INCLUDE ssSQL14] SP 3, and [!INCLUDE sssql16-md] SP 1 and later versions.

Permissions

For [!INCLUDE sssql19-md] and earlier versions, you require VIEW SERVER STATE permission on the server.

For [!INCLUDE sssql22-md] and later versions, you require VIEW SERVER SECURITY STATE permission on the server.

Related content