I’ll always remember where I was on Tuesday, March 19th, 2019 at 6:55PM.
I was sitting in the Seattle airport, waiting to board the last leg of my flights home to San Diego. I happened to check my phone, and…
It was here.
The update we’d waited for for YEARS had finally landed. Yes, ladies and gentlemen, Microsoft fixed the “String or binary data would be truncated” error! It’s true! Be still, your beating hearts!
KB #4468101 tells you that if you enable trace flag 460, SQL Server will finally tell you just what in the sam hell is going to be truncated.
SP2 CU6 also includes a lot of other fixes like:
- Users are incorrectly permitted to create incremental stats on nonclustered indexes that aren’t aligned to the base table
- Assertion for parallel deletes from filestream tables
- Filestream IO can’t be enabled on cluster shared volumes
- Assertion for linked server queries that point to themselves during a cross-database transaction (talk about a perfect storm)
- MDS database upgrade fails
- Filtered nonclustered columnstore index over a clustered columnstore index may not be maintained (that’s an interesting term for corruption)
- Stack dump during change tracking cleanup
- Data masking doesn’t
Here’s the full list of fixes in SQL 2016 SP2 CU6, and also today, Microsoft dropped 2016 SP1 CU14. But screw that one – that one doesn’t have our sweet, sweet #4468101 fix. Go get you some of that.
Noooooooooo! I listed the ability to correctly identify the string on my resume under special skills! I’m ruined.
I think the biggest question here is why would they make this optional? Who would ever say
“nope I like the old way better”
Who? People that still have televisions with knobs to change the channels.
Good point perhaps the same people who still use the pager option for Job Step Errors
The biggest reason it’s optional: anytime you change behavior in SQL Server, you can end up with bugs. Trace flag 460 has a bug described in this morning’s upcoming post: https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/
Why optional indeed!
“Well, our code doesn’t really account for that response from the SQL Server..”
-Every Dev Team Ever. 😉
Anyone have an issue with VSS backups after applying? We applied CU6 to 2 servers out of 4. Once applied we receive the below message and it sends the SQLServerWriter into Non-Retryable state until I restart the SQL VSS service. No error messages in the SQL logs, but also no longer seeing the I/O Freeze and I/O resumed messages just before the backup process. SSMS backups are working fine, and Azure shows snaps are fine.
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 102
Error state: 1, Severity: 15
Source: Microsoft SQL Server Native Client 11.0
Error message: Incorrect syntax near ‘.’.
D – open a support call right away. There have been a lot of VSS bugs in the last several CUs, and I wouldn’t be surprised if you were hitting a new one. Don’t dilly-dally around with blog post comments – when your backups fail, call Microsoft, full stop.