Edit

Data-tier Application Tables - sysdac_history_internal

Applies to: SQL Server

Contains information about the actions taken to manage data-tier applications (DAC). This table is stored in the dbo schema of the msdb database.

Column name Data type Description
action_id int Identifier of the action
sequence_id int Identifies a step within an action.
instance_id uniqueidentifier Identifier of the DAC instance. This column can be joined on the instance_id column in dbo.sysdac_instances.
action_type tinyint Identifier of the action type:

0 = deploy
1 = create
2 = rename
3 = register
4 = create objects
5 = detach
6 = delete
7 = data transfer
8 = disable constraints
9 = move data
10 = enable constraints
11 = copy permissions
12 = set readonly
13 = upgrade
14 = unregister
15 = update registration
16 = set read/write
17 = disconnect users
action_type_name varchar(19) Name of the action type:

- deploy
- create
- rename
- register
- create objects
- detach
- delete
- data transfer
- disable constraints
- move data
- enable constraints
- copy permissions
- set readonly
- upgrade
- unregister
- update registration
- set readwrite
- disconnect users
dac_object_type tinyint Identifier of the type of object affected by the action:

0 = dacpac
1 = login
2 = database
dac_object_type_name varchar(8) Name of the type of object affected by the action:

- dacpac = DAC instance
- login
- database
action_status tinyint Code identifying the current status of the action:

0 = not started
1 = pending
2 = success
3 = fail
4 = rollback
action_status_name varchar(11) Current status of the action:

- not started
- pending
- success
- fail
- rollback
required bit Used by the Database Engine when rolling back a DAC operation.
dac_object_name_pretran sysname Name of the object before the transaction containing the action is committed. Used only for databases and logins.
dac_object_name_posttran sysname Name of the object after the transaction containing the action is committed. Used only for databases and logins.
sqlscript nvarchar(max) Transact-SQL script that implements an action on a database or login.
payload varbinary(max) DAC package definition saved in a binary encoded string.
comments varchar(max) Records the login of a user who accepted potential data loss in a DAC upgrade.
error_string nvarchar(max) Error message generated if the action encounters an error.
created_by sysname The login that launched the action that created this entry.
date_created datetime The date and time this entry was created.
date_modified datetime The date and time the entry was last modified.

Remarks

DAC management actions, such as deploying or deleting a DAC, generate multiple steps. Each action is assigned an action identifier. Each step is assigned a sequence number and a row in sysdac_history_internal, where the status of the step is recorded. Each row is created when the action step starts, and is updated as needed to reflect the status of the operation. For example, a deploy DAC action could be assigned action_id 12 and get four rows in sysdac_history_internal:

action_id sequence_id action_type_name dac_object_type_name
12 0 create dacpac
12 1 create login
12 2 create database
12 3 rename database

DAC operations, such as delete, don't remove rows from sysdac_history_internal. You can use the following query to manually delete the rows for DACs no longer deployed on an instance of the Database Engine:

DELETE msdb.dbo.sysdac_history_internal
WHERE instance_id NOT IN (
    SELECT instance_id FROM msdb.dbo.sysdac_instances_internal
);

Deleting rows for active DACs doesn't affect DAC operations; the only effect is that you can't report the full history for the DAC.

Note

Currently, there's no mechanism for deleting sysdac_history_internal rows on Azure SQL Database.

Permissions

Requires membership in the sysadmin fixed server role. Read-only access to this view is available to all users with permissions to connect to the master database.

Related content

  • Last updated on