Home/Error Library/sqlserver-8152-string-or-binary-data-truncated

SQL Server

SQL Server 2016–2022Data Integrity & Types

7 min read

Msg 8152, Level 16, State 14: String or binary data would be truncated

SQL Server Msg 8152 — Which Row and Column is Truncating?

Finding culprit columns and rows in Msg 8152, leveraging SQL Server 2019+ Msg 2628 and trace flag 460, and avoiding silent data loss from ANSI_WARNINGS OFF.

Reviewed by:Principal Database Reliability Architect

Tested Versions: SQL Server 2022 CU15 & 2019 CU28 (Uyumluluk Düzeyi 150 & 160) | Tarihsel: SQL Server 2016 SP2 CU6 (Trace Flag 460)

Operational Verification & Reliability Metadata

Last Technical Review: 30 Ekim 2026

Tested VersionsSQL Server 2022 & 2019 (Compat 150/160, Msg 2628) | SQL Server 2016 SP2 CU6 (Trace Flag 460)

Required PrivilegesALTER TABLE on target; sysadmin for DBCC TRACEON; ALTER DATABASE for SCOPED CONFIGURATION

Risk Assessment

HighSilencing the error via ANSI_WARNINGS OFF causes silent data loss and violates regulatory audit integrity

Expected OutputInstant identification of violating column and value via Msg 2628/TF 460 or DATALENGTH analysis

Rollback Procedure:Keep ANSI_WARNINGS ON, proportionally widen target columns, or enforce upstream staging validation

Microsoft Docs: Message 2628 Truncation Details

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

plaintext

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

The most frustrating aspect of Msg 8152 is its complete lack of diagnostic context: It fails to specify which column, which row, or which value exceeded the schema definition. When running bulk ETL pipelines or executing multi-column INSERT ... SELECT statements, developers are left guessing blindly.

IMPORTANT

Immediate Production Tip: SQL Server 2019+ Msg 2628 and Trace Flag 460 Starting in SQL Server 2019, if the database compatibility level is set to 150 or higher, SQL Server replaces Msg 8152 with the verbose and actionable Msg 2628: ``

plaintext
Msg 2628, Level 16, State 1, Line 1
String or binary data would be truncated in table 'app.dbo.Customer', column 'LastName'.
Truncated value: 'Alexander-Montgomery-Wellington...'.


On SQL Server 2016 SP2 CU6 and 2017 CU12+, you can enable this exact behavior using **Trace Flag 460**:

sql
DBCC TRACEON (460, -1);
-- or at the database scope:
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;

`` This single setting reduces troubleshooting time from hours to seconds!

▸Locating the Culprit Column Without Msg 2628

If running on older releases without Trace Flag 460, isolate the column using metadata and staging analysis:

1. Inspect Column Definitions and Storage Lengths

sql

SELECT c.name AS column_name, t.name AS data_type, c.max_length AS bytes,
       CASE WHEN t.name LIKE 'n%' THEN c.max_length / 2 ELSE c.max_length END AS char_limit,
       c.is_nullable
FROM sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Customer')
ORDER BY c.column_id;

WARNING

Bytes vs Characters: sys.columns.max_length reports storage size in bytes. For nvarchar(50), the max length is 100 bytes (2 bytes per Unicode code point). Forgetting this distinction leads to investigating the wrong column.

2. Query Candidate Violations in Staging Data

sql

SELECT TOP 10 LastName, LEN(LastName) AS char_count, DATALENGTH(LastName) AS byte_size
FROM staging.Customer
WHERE DATALENGTH(LastName) > 100        -- target nvarchar(50) = 100 bytes
ORDER BY DATALENGTH(LastName) DESC;

LEN() counts characters excluding trailing spaces, while DATALENGTH() computes true physical byte consumption.

▸The Silent Truncation Anti-Pattern

A catastrophic workaround occasionally seen in legacy code is disabling ANSI warnings:

sql

SET ANSI_WARNINGS OFF;
INSERT INTO dbo.Customer (LastName) VALUES ('Excessively long customer name that overflows');
-- Statement succeeds with ZERO errors, but data is SILENTLY truncated!

CAUTION

Severe Data Integrity Risk: SET ANSI_WARNINGS OFF does not fix the problem; it masks it. Receiving Msg 8152 is an essential safeguard guaranteeing zero data corruption. Disabling warnings causes silent truncation of legal names, addresses, and cryptographic hashes without triggering any alert.

▸Step-by-Step Resolution

1. Measure and Widen the Column Proportionally

Avoid the knee-jerk reaction of switching columns directly to nvarchar(max):

•(max) columns cannot be used as index key columns.
•Query workspace memory grants scale aggressively for (max) columns, exhausting server concurrency limits.
•Instead, measure true 99th percentile requirements:

sql

SELECT MAX(LEN(LastName)) AS max_length,
       PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY LEN(LastName)) OVER () AS p99_length
FROM staging.Customer;

Widen the schema accurately:

sql

ALTER TABLE dbo.Customer ALTER COLUMN LastName nvarchar(150) NULL;

On Enterprise Edition, use WITH (ONLINE = ON) to prevent schema modification locks.

2. Enforce Ingestion Validation in Upstream Pipelines

Incorporate column length validation assertions in your ETL orchestrator (SSIS, ADF, Airflow) so violating batches are routed to dead-letter quarantine tables rather than aborting batch transactions midway.

▸Related Error Guides & Hub Links

•SQL Server Foreign Key Constraint 547 (H-23) — Referential integrity violations
•SQL Server Duplicate Key Unique Index 2601 (H-26) — Unique constraint collisions
•SQL Server Hub — Data types and database reliability engineering

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (50)

SQLSERVER5 min