Seeing More with Trace Flag 460

I came across this blog post a while back explaining the details of trace flag 460. I had never used this trace flag but found it interesting enough to make a mental note of it. As it turns out, it wasn’t long before I could put trace flag 460 to use to better troubleshoot an issue. Since this trace flag helped me, I thought it was worthy of a blog post.

Why Use Trace Flag 460?

In SQL Server 2016 and 2017, trace flag 460 can be used to gather additional details about string truncation errors. You may be familiar with the “String or binary data would be truncated” error message. Have you been left wondering what would be truncated? This is where trace flag 460 comes in. When enabled, the error message will include details on where exactly the potential truncation is taking place.

See For Yourself

Let’s say we’re on a SQL Server 2016 compatibility level. We’ll start with this table:

CREATE TABLE TraceTest (
	Id INT IDENTITY(1, 1) PRIMARY KEY,
	TestString VARCHAR(10)
);
GO

If we try to insert a value longer than the max of 10, we’ll get an error:

INSERT INTO TraceTest
VALUES ('WAY TOO LONG');
GO

There’s that familiar error message mentioned above. We know “something” would be truncated, but we don’t have anything else to go on. Let’s enable trace flag 460:

DBCC TRACEON (460, -1);

If we run our insert again, we’ll receive a more detailed error message:

Now our error message includes the table, column, and value.

In this example, the answer is obvious. But if you’re encountering these errors in the real world with more complicated code and procedures, the detailed error is going to be a big help.

Trace Flag 460 and Newer Versions

Do you still need to turn on trace flag 460 with more current SQL Server versions? Probably. I’ll once again point you to this post for more details.

Thanks for reading!