Do you need Trace Flag 460?

For most people that are reading this post, I’d venture to guess that you have no idea what Trace Flag 460 is or when you would use it. Well first off, let me tell you it’s a fully documented and supported trace flag, and it’s totally safe. In fact, on SQL Server 2019 and higher it is the default behavior when you create a new database. What does it do? It makes troubleshooting string or binary truncation issues easier by changing the error message that is returned from message ID 8152 and replaces it instead with message ID 2628. The trace flag is also available in SQL Server 2016 SP2 CU6+ and SQL Server 2017 CU12.

KB4468101 – Improvement: Optional replacement for “String or binary data would be truncated” message with extended information in SQL Server 2016 and 2017 – Microsoft Support

However, while the Trace Flags BOL topic documents this, there is a somewhat easy to misunderstand note that exists for this trace flag.

Note: Starting with database compatibility level 150, message ID 2628 is the default and this trace flag has no effect.

If you took this to mean that you would no longer need the trace flag on SQL Server 2019 and higher, I would totally understand why. However, that would be an incorrect interpretation of the note. When a database is at compatibility level 150 in SQL Server 2019, and the database scoped configuration for VERBOSE_TRUNCATION_WARNINGS is ON for the database, the trace flag has no effect. If you lower the database compatibility level from 150 however, the trace flag would still need to be enabled to get the truncation warning enhancement from message ID 2628. This is documented in the BOL topic for the database scoped configuration options for VERBOSE_TRUNCATION_WARNINGS:

For database compatibility level 140 or lower, error message 2628 remains an opt-in error message that requires trace flag 460 to be enabled, and this database scoped configuration has no effect.

So, yes you might still want to include this trace flag as one of the startup parameters on SQL Server 2016 and higher if you’d like to benefit from a more verbose error message when string or binary truncation occurs during a data loading operation.

One thought on “Do you need Trace Flag 460?”

Randolph West says:

April 24, 2023 at 12:27 am

Jon, I own this page on SQL Docs, so I’m going to expand the note to be less confusing.

Thanks for this post. I just wish I’d seen it a month ago.

Reply

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Do you need Trace Flag 460?

One thought on “Do you need Trace Flag 460?”