Edit

Data-tier Application Tables - sysdac_history_internal

  • Article

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 (Transact-SQL).
action_type tinyint Identifier of the action type:

0 = deploy

1 = create

2 = rename

3 = detach

4 = delete
action_type_name varchar(19) Name of the action type:

deploy

create

rename

detach

delete
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 = pending

1 = success

2 = fail
action_status_name varchar(11) Current status of the action:

pending

success

fail
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, do not 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 FROM msdb.dbo.sysdac_history_internal  
WHERE instance_id NOT IN  
   (SELECT instance_id  
    FROM msdb.dbo.sysdac_instances_internal);

Deleting rows for active DACs does not impact DAC operations; the only impact is that you will not be able to report the full history for the DAC.

Note

Currently, there is 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.

See Also

Data-tier Applications
dbo.sysdac_instances (Transact-SQL)
sysdac_instances_internal (Transact-SQL)