Skip to main content
Delphix

Resolving SQL server error: Msg 3609, Level 16, State 2 - The transaction ended in the trigger. The batch has been aborted. (KBA6936)

 

KBA

KBA# 6936

 

Issue

Running a job from the Delphix engine for a dSource or a VDB fails with:

Msg 3609, Level 16, State 2, Server SERVER_NAME, Line 1
The transaction ended in the trigger. The batch has been aborted.

 

Prerequisites

1) Delphix engine, any version
2) MSSQL environment, staging or target instance, any supported version

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0

5.3

5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

Resolution

Disable or drop the SQL server DDL triggers and run the job again.

Troubleshooting

It is possible to check for the SQL server DDL triggers via T-SQL or on Microsoft SQL management studio.

T-SQL:

select name,is_disabled from sys.server_triggers
go

You can determine details about the trigger using Microsoft SQL Server Management Studio (SSMS):

  1. After connecting to the SQL Server instance in question, navigate to Server Objects -> Triggers
  2. Review the code of each trigger:

    If "Disable" is available and "Enable" grayed out, it means that the trigger is currently enabled.

    Example of code view:

The above trigger prevents all databases from being dropped.  When the Delphix jobs involving the deleting of staging or VDB databases encounter the section to DROP the object, the failure occurs.  From a user's standpoint, this is to prevent external DROP commands from executing.

 


Related Articles

The following articles may provide more information or related information to this article:

External reference: 

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/get-information-about-ddl-triggers?view=sql-server-ver15