sys.dm_os_child_instances (Transact-SQL)

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

Returns a row for each user instance that has been created from the parent server instance.

IMPORTANT! This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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 SQL Server Management Studio 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 SQL Server 2012 Express only.

NOTE To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_child_instances.

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: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Permissions

Requires VIEW SERVER STATE permission on the server.

Remarks

For more information about dynamic management view, see Dynamic Management Views and Functions (Transact-SQL) in SQL Server Books Online.

See Also

User Instances for Non-Administrators