MetricSign
Start free
Low severityquery

SQL Server Error:
3621

What does this error mean?

SQL Server raises error 3621 whenever a preceding error forces the current statement to terminate and roll back. Error 3621 is never the root cause — it is a secondary confirmation message that always appears after the actual error (e.g. 2627 for duplicate key, 8115 for arithmetic overflow, 229 for permission denied). In data pipelines, 3621 typically surfaces when an ADF Copy Activity, Stored Procedure Activity, or linked-service query hits a constraint violation or permission issue on the target SQL Server or Azure SQL database. The symptom is a failed activity with two or more errors stacked: the real error first, then 3621 as the trailing termination notice. Fix the first error in the stack — 3621 disappears automatically.

Common causes

  • 1A UNIQUE or PRIMARY KEY constraint violation (error 2627/2601) during INSERT — the duplicate row causes the statement to fail, and 3621 follows as the termination notice
  • 2An arithmetic overflow or type conversion error (error 8115/245) when a column value exceeds the target data type — common in ADF Copy Activities with schema drift or implicit conversions
  • 3A trigger on the target table raises an error or violates a constraint, causing both the trigger and the original statement to roll back with 3621
  • 4Insufficient permissions (error 229/230) on the target table or schema — the service principal or SQL login used by ADF or the linked service lacks INSERT, UPDATE, or EXECUTE rights
  • 5A user-defined error raised via RAISERROR or THROW with severity >= 16 inside a stored procedure, causing the calling statement to terminate
  • 6CHECK constraint or FOREIGN KEY violation (error 547) when inserted or updated data references a nonexistent parent key or fails a column-level check
  • 7SET XACT_ABORT ON is active and any error in the batch causes the entire transaction to abort — 3621 appears for each terminated statement in the batch

How to fix it

  1. 1Step 1: Identify the primary error. In SSMS or Azure Data Studio, run: SELECT TOP 50 message_id, severity, text FROM sys.messages WHERE message_id IN (SELECT DISTINCT error_number FROM yourErrorLog) ORDER BY message_id; — or in ADF, open the failed activity → Error details → expand the full message stack. The first error is the actionable one.
  2. 2Step 2: For constraint violations (2627, 2601, 547), find the conflicting rows: SELECT * FROM staging_table s INNER JOIN target_table t ON s.pk_column = t.pk_column; — then decide whether to use MERGE, add WHERE NOT EXISTS logic, or truncate-and-reload.
  3. 3Step 3: For type conversion errors (8115, 245), check the source data against the target schema: SELECT column_name, data_type, character_maximum_length FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'your_table'; — then cast or widen the target column.
  4. 4Step 4: For permission errors (229, 230), grant the required permission to the pipeline identity: GRANT INSERT, UPDATE ON schema.table TO [your-adf-managed-identity]; — verify with: SELECT dp.name, p.permission_name, p.state_desc FROM sys.database_permissions p JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id WHERE dp.name = 'your-identity';
  5. 5Step 5: For trigger-related failures, test the statement outside the pipeline: INSERT INTO target_table (col1, col2) VALUES ('test', 1); — if the trigger fires and fails, fix the trigger logic or the data that violates the trigger's checks.
  6. 6Step 6: If SET XACT_ABORT ON causes cascading rollbacks across multiple statements, isolate critical statements in separate TRY...CATCH blocks: BEGIN TRY INSERT INTO ... END TRY BEGIN CATCH SELECT ERROR_NUMBER(), ERROR_MESSAGE(); END CATCH;
  7. 7Step 7: After fixing the root cause, rerun the failed ADF pipeline activity or SQL batch. Confirm 3621 no longer appears in sys.dm_exec_requests or the ADF monitoring output.

Example log output

Msg 2627, Level 14, State 1, Line 8
Violation of UNIQUE KEY constraint 'UQ_orders_external_id'. Cannot insert duplicate key in object 'dbo.orders'. The duplicate key value is (ORD-2026-48812).
Msg 3621, Level 0, State 0, Line 8
The statement has been terminated.

Frequently asked questions

Why does SQL Server raise error 3621 alongside other errors?

SQL Server uses 3621 to confirm that a statement was fully terminated and rolled back after a prior error. It distinguishes between statements that completed partially and those that were entirely undone. The pattern is always: primary error first, then 3621.

Can error 3621 appear without another error?

In practice, no. Error 3621 is always paired with the error that triggered the termination. If you only see 3621 in your logs, your logging configuration is likely filtering lower-severity messages. Check the ADF full error output or run EXEC sp_readerrorlog 0, 1, '3621' in SSMS to find the surrounding messages.

Does error 3621 mean my data was rolled back?

Yes — the specific statement that produced 3621 was terminated and all its changes were rolled back. However, earlier statements in the same batch that already committed are not affected, unless SET XACT_ABORT ON is enabled. With XACT_ABORT ON, the entire transaction rolls back, not just the failing statement.

Will retrying the ADF pipeline fix error 3621?

Only if the root cause is transient (e.g. a deadlock or timeout). For constraint violations, permission errors, or type mismatches, retrying produces the same failure. Fix the underlying data or configuration issue first, then rerun. ADF default retry counts (typically 0 for Copy Activities) will not help with persistent errors.

Source · learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors

Other query errors