| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_os_child_instances (Transact-SQL) |
sys.dm_os_child_instances (Transact-SQL) |
rwestMSFT |
randolphwest |
02/27/2023 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-2016||>=sql-server-linux-2017||>=aps-pdw-2016||=azure-sqldw-latest |
[!INCLUDE sql-asa-pdw]
Returns a row for each user instance that has been created from the parent server instance.
Important
[!INCLUDEssNoteDepFutureAvoid]
The information returned from sys.dm_os_child_instances can be used to determine the state of each User Instance (heart_beat) and to obtain the pipe name (instance_pipe_name) that can be used to create a connection to the User Instance using [!INCLUDEssManStudioFull] or SQLCmd. You can only connect to a User Instance after it has been started by an external process, such as a client application. SQL management tools cannot start a User Instance.
Note
User Instances are a feature of [!INCLUDEssexpress-2012-md] only.
Note
To call this from [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_child_instances. [!INCLUDEsynapse-analytics-od-unsupported-syntax]
| Column | Data type | Description |
|---|---|---|
| owning_principal_name | nvarchar(256) | The name of the user that this user instance was created for. |
| owning_principal_sid | nvarchar(256) | SID (Security-Identifier) of the principal who owns this user instance. This matches Windows SID. |
| owning_principal_sid_binary | varbinary(85) | Binary version of the SID for the user who owns the user Instance |
| instance_name | nvarchar(128) | The name of this user instance. |
| instance_pipe_name | nvarchar(260) | When a user instance is created, a named pipe is created for applications to connect to. This name can be used in a connect string to connect to this user instance. |
| os_process_id | Int | The process number of the Windows process for this user instance. |
| os_process_creation_date | Datetime | The date and time when this user instance process was last started. |
| heart_beat | nvarchar(5) | Current state of this user instance; either ALIVE or DEAD. |
| pdw_node_id | int | Applies to: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
Requires VIEW SERVER STATE permission on the server.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
For more information about dynamic management view, see Dynamic Management Views and Functions (Transact-SQL) in [!INCLUDEssNoVersion] Books Online.