Msg 8152, String or Binary Data Would Be Truncated

You're loading data. Maybe it's an ETL, maybe it's a migration... and this happens:

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

The data doesn't fit. A value exceeds the column's defined length. But which column? Which row? The error doesn't say.

The Fix: Get a Better Error Message

SQL Server can tell you exactly which table, column, and data value caused the problem, like this:

Msg 2628, Level 16, State 1
String or binary data would be truncated in table 'dbo.Customers', column 'LastName'.
Truncated value: 'Wolfeschlegelsteinhausen'.

But to see this message rather than the useless one, you need one of the following:

Your Situation	How to Get the Detailed Error
SQL Server 2019+ compat 150+	You already have it (default)
SQL Server 2019+ compat < 150	Change compatibility level or trace flag 460
SQL Server 2016 SP2 CU6+ / 2017 CU12+	Trace flag 460
Older	Manual column comparison

Check Your Compatibility Level

If you're on SQL Server 2019 but still seeing the old Msg 8152, your database probably has a lower compatibility level (common after migrations):

SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabase';

If the result is below 150, that's your problem. To upgrade (Test first! This affects query optimizer behavior):

ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 150;

Enable Trace Flag 460

If you're on SQL Server 2016/2017 and cannot change the compatibility level, you can enable trace flag 460. This flag replaces the generic truncation message with an enhanced message including the tablename, column and data value that caused the truncation. Huge improvement to Microsoft's vague error messages.

You can enable Trace Flag 460 temporarily on the single query that is failing OR you can set it permanently at the server level. This is my preference. Leaving Trace Flag 460 enabled has negligible overhead and is considered safe for continuous use.

** This flag is not needed v2019+ with compatibility 150 or higher, and there was a known bug for this trace flag on v2017 CU13, but this was corrected in CU14 (KB4490142).

Option 1: Single Query

INSERT INTO Customers (FirstName, LastName, Email)
SELECT FirstName, LastName, Email FROM StagingTable
OPTION (QUERYTRACEON 460);

Option 2: Enable Permanently at Server Level

1. Open SQL Server Configuration Manager (run as Administrator)
2. In the left pane, select SQL Server Services
3. Right-click your SQL Server instance and select Properties
4. Go to the Startup Parameters tab
5. In the "Specify a startup parameter" box, type: -T460
   (uppercase T, no space)
6. Click Add, then Apply, then OK
7. Restart the SQL Server service for the change to take effect

Verify the Flag is Active

After the service restart, verify the flag is enabled with this query in SSMS:

DBCC TRACESTATUS(460, -1);
GO

DEMO

-- Create a table with a narrow column
CREATE TABLE TruncDemo (
    ID INT,
    ProductName VARCHAR(10)
);

-- Try to insert data that's too long
INSERT INTO TruncDemo (ID, ProductName)
VALUES (1, 'Industrial Widget Pro Max');

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

With trace flag 460:

-- Enable the flag and try again
INSERT INTO TruncDemo (ID, ProductName)
VALUES (1, 'Industrial Widget Pro Max')
OPTION (QUERYTRACEON 460);

Msg 2628, Level 16, State 1, Line 8
String or binary data would be truncated in table 'DBA.dbo.TruncDemo', column 'ProductName'. Truncated value: 'Industrial'.
The statement has been terminated.

Now you know the table, column and exact data value that didn't fit.

Older Versions

Regrettably, there is no built-in help. You need to compare source and target column lengths manually:

SELECT 
    c.name ColumnName,
    t.name DataType,
    c.max_length
FROM sys.columns c JOIN sys.types t 
  ON c.user_type_id = t.user_type_id
WHERE object_id = OBJECT_ID('YourTargetTable')
ORDER BY c.column_id;

AI Tools

If you paste the error message, failing INSERT and table definitions into Claude or ChatGPT, they will identify the offending column/data value that produced the truncation. No special technique — just show the AI the schema and the error.

More to Read

Microsoft: Trace Flags
Brent Ozar: How to Fix the Error
Microsoft Tech Community: Replacing the Infamous Error 8152