Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

String or binary data would be truncated

29 May,2020 by Tom Collins

Question: I'm trying to run an INSERT statement , on a table with a large amount of columns and millions of rows. When executing the query statement , I'm getting an error message:

SELECT * FROM myTable1 ;

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


 How can I fix this issue? Trying to find which specific row and column is causing the problem is the immediate problem. Once I can find which data is triggering the error , I can make the adjustment 

Answer: This is one of the most common errors with INSERT statements. Often trying to find the source of the issue is tricky. Especially if you do not have access to the the column definitions from the source\ target tables. 

SQL Server 2017  (CU12 and above) introduced the trace flag 460. 

Taking the code you supplied . The trace flag 460 can be enabled for a session  or as a permanent at startup 

Method 1 - enable a trace for a sessions



SELECT * FROM myTable1 ;



Method 2 - Set up as a SQL Server startup parameter

How to enable SQL Server trace flags at startup


Now when you execute the statement , you'll get something like

Msg 2628, Level 16, State 1, Line 21
String or binary data would be truncated in table 'mydb.dbo.myTable', column 'myColumn1'. Truncated value: 'some_value'.


The the information supplied in this error message and make any adjustments required on either the data , column width or data type.

If you are using SQL Server 2019 - there is the VERBOSE_TRUNCATION_WARNINGS  option - enabled through the ALTER DATABASE SCOPED CONFIGURATION


Read More on DBCC and trace flags

List SQL Server DBCC commands using trace 2520 – documented and undocumented commands


Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on String or binary data would be truncated | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer