Msg 8152 Still Showing in SQL Server 2019: Check Compatibility Level

Msg 8152 still shows up in SQL Server 2019 for some databases, and the compatibility level decides why. I covered the new message in String or Binary Data Would Be Truncated: Find the Column With Msg 2628.

The Error

This is the old message:

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

What the Reader Sent

The release notes say Microsoft improved this error in SQL Server 2019. I get questions about it in my Comprehensive Database Performance Health Check.

A reader had migrated from SQL Server 2017 to SQL Server 2019 and couldn’t see the improvement. They sent me this script and a screenshot. The script drops and recreates a database named TestTruncationMessage, so run it only where that’s fine.

SET NOCOUNT ON
GO
SELECT SERVERPROPERTY('ProductVersion')
GO
USE master
GO
DROP DATABASE IF EXISTS TestTruncationMessage
GO
Create database TestTruncationMessage
GO
use TestTruncationMessage
GO
CREATE TABLE MyTable (i INT, j VARCHAR(10))
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters')
GO
USE [TestTruncationMessage]
GO
select * from sys.database_scoped_configurations where name = 'VERBOSE_TRUNCATION_WARNINGS'
GO

The reader was on SQL Server 2019, and the setting VERBOSE_TRUNCATION_WARNINGS was on. The message was still the old one.

Why It Happens

The documentation for ALTER DATABASE SCOPED CONFIGURATION explains it. In SQL Server 2019, Msg 2628 is the default under database compatibility level 150. You can go back to Msg 8152 with the setting VERBOSE_TRUNCATION_WARNINGS, or with a lower compatibility level.

I checked the reader’s database. Its compatibility level was 140, because it was an upgrade from SQL Server 2017.

The Fix

To get Msg 2628, the database needs compatibility level 150. Test your application before you raise it. This script shows the difference between the two levels:

ALTER DATABASE [TestTruncationMessage] SET COMPATIBILITY_LEVEL = 140
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters')
GO
ALTER DATABASE [TestTruncationMessage] SET COMPATIBILITY_LEVEL = 150
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters')
GO

The compatibility level changes the message. The database scoped configuration controls it too. Here is a quick demonstration:

SELECT DATABASEPROPERTYEX('TestTruncationMessage', 'CompatibilityLevel') 'CurrentCompatibilityLevel'
GO
USE [TestTruncationMessage]
GO
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = OFF;
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters')
GO
USE [TestTruncationMessage]
GO
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;
GO
INSERT INTO MyTable VALUES (1,'This is more than 10 characters')
GO

With the setting OFF you get Msg 8152. With it ON, at compatibility level 150, you get Msg 2628.

How to Prevent It

After an upgrade, check the compatibility level of your databases. A database upgraded from SQL Server 2017 keeps level 140 until you change it.

Let me know if this post was useful. Leave your feedback in the comments, and I’ll reply.

Msg 8152 is not a missing feature, it is a database still running at level 140.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.