SQL Server 2019 Preview (CTP 2.0) introduced a long-awaited improvement to an error message that’s been around in SQL Server for many years, but was unhelpful:
Msg 8152, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated.
Thanks for nothing, error message. Which table? Which column? What data?
This is how the error message looks now:
Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.
Notice how the table, column and value are all mentioned in the error message now, which makes debugging and troubleshooting much easier. Thank you Microsoft!
How to make use of this new information
Microsoft has a long-standing policy about maintaining backward compatibility, so turning on this feature is optional, enabled with Trace Flag 460. Here’s how to do it on both Windows and Linux:
SQL Server 2017 on Windows
On Windows, we can enable a Trace Flag using SQL Server Configuration Manager. If we’re on Windows 10 or Windows Server 2016, we might need to manually locate Configuration Manager.
Under SQL Server Services, locate the SQL Server service, right-click on it, and choose Properties.
Go to the Startup Parameters tab, and type
-T460 (note that there is no space between -T and the Trace Flag number).
Click Add, and then click Apply. We should see the Trace Flag in the list of Existing Parameters now.
After clicking OK, the final step is to restart the SQL Server service for the change to take effect. We can do this by right-clicking on the SQL Server service again, and choosing Restart. This will also restart the SQL Server Agent if it is running.
SQL Server 2017 on Linux
On SQL Server on Linux (note that this includes SQL Server in Docker containers), we can add a Trace Flag to the SQL Server instance using the command line tool
From the command line of our SQL Server instance, run the following command to add Trace Flag 460.
sudo /opt/mssql/bin/mssql-conf traceflag 460 on
As with Windows, we need to restart the SQL Server service to effect this change.
sudo systemctl restart mssql-server
What about older versions of SQL Server?
Although it is not available in SQL Server 2016 yet, we might expect this in the near future. I certainly wouldn’t hold my breath for SQL Server 2014 and older versions.
And remember, SQL Server 2008 and 2008 R2 are at their end of life in July 2019. If you have not upgraded to at least SQL Server 2014 by now, you have a lot of work ahead of you for the next two years.
Share your thoughts about SQL Server error messages in the comments below.