sysdac_history_internal (Transact-SQL)

 

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

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 nameData typeDescription
action_idintIdentifier of the action
sequence_idintIdentifies a step within an action.
instance_iduniqueidentifierIdentifier of the DAC instance. This column can be joined on the instance_id column in dbo.sysdac_instances (Transact-SQL).
action_typetinyintIdentifier of the action type:

 0 = deploy

 1 = create

 2 = rename

 3 = detach

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

 deploy

 create

 rename

 detach

 delete
dac_object_typetinyintIdentifier of the type of object affected by the action:

 0 = dacpac

 1 = login

 2 = database
dac_object_type_namevarchar(8)Name of the type of object affected by the action:

 dacpac = DAC instance

 login

 database
action_statustinyintCode identifying the current status of the action:

 0 = pending

 1 = success

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

 pending

 success

 fail
RequiredbitUsed by the Database Engine when rolling back a DAC operation.
dac_object_name_pretransysnameName of the object before the transaction containing the action is committed. Used only for databases and logins.
dac_object_name_posttransysnameName of the object after the transaction containing the action is committed. Used only for databases and logins.
sqlscriptnvarchar(max)Transact-SQL script that implements an action on a database or login.
payloadvarbinary(max)DAC package definition saved in a binary encoded string.
Commentsvarchar(max)Records the login of a user who accepted potential data loss in a DAC upgrade.
error_stringnvarchar(max)Error message generated if the action encounters an error.
created_bysysnameThe login that launched the action that created this entry.
date_createddatetimeThe date and time this entry was created.
date_modifieddatetimeThe date and time the entry was last modified.

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_idsequence_idaction_type_namedac_object_type_name
120createdacpac
121createlogin
122createdatabase
123renamedatabase

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.

System_CAPS_ICON_note.jpg Note


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

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.

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

Community Additions

ADD
Show: