Blog - Brent Ozar

How to fix the error “String or binary data would be truncated”

March 20, 2019

To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level.

First, let’s see the error happen: let’s create a table with small fields, and then try to insert more data than it holds.

CREATE TABLE dbo.CoolPeople(PersonName VARCHAR(20), PrimaryCar VARCHAR(20));
GO
INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD');
GO

CREATE TABLE dbo.CoolPeople(PersonName VARCHAR(20), PrimaryCar VARCHAR(20));

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)

VALUES ('Baby', '2006 Subaru Impreza WRX GD');

Baby’s car is longer than 20 characters, so when the insert statement runs, we get an error:

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

Msg 8152, Level 16, State 30, Line 5

String or binary data would be truncated.

The statement has been terminated.

That sucks because we have no idea which field is causing the problem! It’s especially terrible when you’re trying to insert lots of rows.

To fix it, turn on trace flag 460.

Trace flag 460 was introduced in SQL Server 2016 Service Pack 2, Cumulative Update 6, and in SQL Server 2017. (You can find & download the most recent updates at SQLServerUpdates.com.) You can turn it on at the query level, like this:

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD')
OPTION (QUERYTRACEON 460);
GO

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)

VALUES ('Baby', '2006 Subaru Impreza WRX GD')

OPTION (QUERYTRACEON 460);

And now when the query runs, it shows you which column is getting clipped, and which row, too. In our case, we’ve only got one row – but in your real-life data, you’ll be much happier knowing which row’s triggering the error:

Msg 2628, Level 16, State 1, Line 9
String or binary data would be truncated in 
table 'StackOverflow2013.dbo.CoolPeople', column 'PrimaryCar'. 
Truncated value: '2006 Subaru Impreza '.

Msg 2628, Level 16, State 1, Line 9

String or binary data would be truncated in

table 'StackOverflow2013.dbo.CoolPeople', column 'PrimaryCar'.

Truncated value: '2006 Subaru Impreza '.

You can turn on this trace flag at the query level as shown above, or at the server level:

DBCC TRACEON(460, -1);
GO

1 2	DBCC TRACEON(460, -1); GO

That turns it on for everybody, not just you – so get everybody on your team to agree before you turn it on. This changes the error message number from 8152 to 2628 (as shown above), which means if you’ve built error handling based on the error number, you’re suddenly going to start getting different behavior.

I’m a fan of turning this trace flag on while I’m doing troubleshooting, and then once I’ve found the culprit, turning it back off again:

DBCC TRACEOFF(460, -1);
GO

1 2	DBCC TRACEOFF(460, -1); GO

In our case, once we’ve identified that Baby’s car is too large, we either need to change his car, or change the datatype in our table to make it bigger, or cast the data on the way in to explicitly strip the extra length off his car. A data chop shop, if you will.

Don’t leave this trace flag enabled.

There’s at least one bug with it as of today on SQL Server 2017 CU13: table variables will throw errors saying their contents are being truncated even when no data is going into them. Andrew Pruski reports:

Here's that error that we were seeing @arrowdrive

Should be fixed now but one to watch out for. https://t.co/WbPrI1YHHJ

— Andrew Pruski ??????? (@dbafromthecold) March 20, 2019

Sadly, that behavior is NOT fixed, and here’s the simplest repro script I could build:

CREATE OR ALTER PROC dbo.Repro @BigString VARCHAR(8000) AS
BEGIN

	DECLARE @Table TABLE ( SmallString VARCHAR(128) )

	IF ( 1 = 0 )
		/* This will never run */
		INSERT INTO @Table	( SmallString )
		VALUES(@BigString)
		OPTION (QUERYTRACEON 460)
END
GO

DECLARE @BigString VARCHAR(8000) = REPLICATE('blah',100)
EXEC dbo.Repro @BigString
GO

CREATE OR ALTER PROC dbo.Repro @BigString VARCHAR(8000) AS

BEGIN

DECLARE @Table TABLE ( SmallString VARCHAR(128) )

IF ( 1 = 0 )

/* This will never run */

INSERT INTO @Table ( SmallString )

VALUES(@BigString)

OPTION (QUERYTRACEON 460)

END

DECLARE @BigString VARCHAR(8000) = REPLICATE('blah',100)

EXEC dbo.Repro @BigString

SQL Server 2017 CU13 still reports that the string will be truncated even though the insert doesn’t run:

Switch out the table variable for a temp table, and it works fine, as expected:

If you want to follow progress on that bug getting fixed, it’s here. Such a great example of why I’m not a fan of using trace flags by default – sure, they can fix issues, but they can also introduce unpredicted, unwanted behaviors. (And, uh, not a fan of table variables either.)

Update 2019/03/25 – the above bug is fixed in 2017 Cumulative Update 14. Yay!

Registration Open for My SQLSaturday Boston Pre-Con

March 19, 2019

Brent Ozar

Conferences and Classes

No Comments

I’m coming to Boston this year for SQLSaturday #877. On Friday, I’ll be teaching a one-day pre-con:

Performance Tuning in 21 Demos – You’re a developer or DBA who wants to tune indexes and queries to make your application go faster. You don’t like learning by watching slides or reading white papers – you want to jump right into the code and see what happens. You’re the kind of person who loves watching YouTube videos that start with, “Hold my beer and watch this.”

Attendees get their choice of a Recorded Class Season Pass or a Consultant Toolkit – so the pre-con pays for itself! Learn more and register now.

Can’t make it to Boston? Here’s where to find me this spring:

April 11: SQLSaturday Israel and pre-con Performance Tuning in 21 Demos
May 20-22: Techorama Belgium and pre-con Performance Tuning in a Day
June 1: SQLSaturday Dallas and pre-con Performance Tuning in 21 Demos
June 8: SQLSaturday South Florida and pre-con Performance Tuning in 21 Demos
June 11-13: SQL Intersection Orlando and pre-con Developer’s SQL Server Recipe Book
August 16-17: SQLSaturday Sacramento and pre-con Performance Tuning in 21 Demos

Poll Results: What Conference Schedule Did You Prefer?

March 18, 2019

Brent Ozar

Conferences and Classes

Last week, I asked you a couple of questions:

When you attend a 1-day conference like a SQLSaturday or SQL Relay, what is your preferred session schedule?

You said:

31.8% (211 votes): 6 60-minute sessions
31.3% (208 votes): A mix of 45-minute and 90-minute sessions
25.3% (168 votes): 7 45-minute sessions: 25.3%
11.6% (77 votes): 5 75-minute sessions

Other ways to think about that:

88.4% do not want 75-minute sessions
56.6% want 45-minute sessions as building blocks, and most of those voters want some of the sessions to be double-length

I know that’s frustrating for some presenters to hear because presenters want all of the time they can get, but the reality is that during a 1-day conference, attendees want more variety.

When you attend a 2-3 day conference like the PASS Summit or SQLbits, what is your preferred session schedule?

You said:

50.2% (333 votes): A mix of 45-minute and 90-minute sessions
20.2% (134 votes): 6 60-minute sessions
18.2% (121 votes): 5 75-minute sessions
(or to put it another way, 81.8% of voters do not want this)
11.4% (76 votes): 7 45-minute sessions

I expected the numbers to be different during a longer event (thus the two separate questions), and they were – but in both cases, they were a strong vote against the default 75-minute session length of many events.

Attendees like 45 minute blocks, not 75.

This confirms something I’ve found when polling my own training class attendees who told me things like:

75 minutes is too long to listen to one speaker without a break
Speakers try to cover too much ground, trying to bring people from 100-level to 300-level in a single session
When given a 75-minute slot, speakers leave too much time for Q&A – when instead they should take questions during the break, next to the podium, after the session completes

As a presenter, my first reaction was to fight the attendees – to tell them they don’t understand what presenters are trying to do, or how we as presenters could do better, or how we need to adapt their expectations.

That doesn’t work.

Attendees are the customers, and we presenters need to spend some time listening and adapting our work to meet the customers’ requests. They want shorter sessions, and they want us to deliver more knowledge in less time.

If you’re a presenter, this means your abstracts need to clearly define your ideal session attendee, then stick to it. As you plan your abstract, write down what the ideal session attendee already knows, and don’t try to cover that stuff in the session. Write down what’s going to be out of scope too – you simply can’t take someone from 100 level to 300 level inside a single 75-minute session. (You certainly can’t do it in 45, either – and if conferences listen to attendee feedback, shorter sessions might start becoming the new normal.)

In my own classes – both one-day pre-cons and multi-day classes – I’ve taken this feedback to heart by aiming to teach in 45-60 minute modules rather than 60-75 minutes. At first, it felt like a more stuttered agenda, taking a 15-minute bio break every 45 minutes, but it does seem to result in more focused attendees that are more able to digest material through the entire day.

What are students saying about my latest round of classes?

March 14, 2019

Brent Ozar

Company News

Mastering Index Tuning has a fresh set of reviews from the latest students.

Levente Kalotai wrote:

Absolutely loving it. The class, Brent style, everything. I have to admit, I had my reservations before the class, but boy I was wrong about it. Less than 5 minutes in the first lab anyone can see the insane amount of work what was put into the class. This admiration only grows through the 3 days as everything is marching toward the last lab. I am really grateful to Brent and his team to use their extensive real-life knowledge and put this class together. Cannot wait till Query Tuning starts, probably I will start the previous recordings ?

Katie Walsh wrote:

This class was just top notch. Brent is very entertaining and he explains the material in such a clear and concise manner. I learned a ton. Looking forward to the next class

Jay Taylor wrote:

Thorough and entertaining. Brent hits Sql Server indexing’s high points, low points and all points in between. Then he gives you a lab to do the work to let the lecture sink in. During the lab, he’ll help you out, in real time. And later you can watch the session again and do the labs again, on your own. It’s a great way to learn. I wish there were similar courses for C#, Angular and Javascript.

Mohammad Darab wrote:

Brent’s way of teaching is unmatched! This class is an absolute must for SQL Server professionals. Perfect amount of lecture, lab time, and Q&A.

Michael DeMore wrote:

The Master Index Tuning class is great. From the way the material is presented, to working the labs I really enjoyed this class. In fact, I have take 4 of Brents classes and they all are wonderful. I do highly recommend. And since I purchased the season pass, I look forward to the next class I take.

Joe D’Aquila wrote:

Excellent! Informative and entertaining. There was the right amount of structure and freedom. The lab machines and tools were top notch. I left the session eager to index tune. I am very much looking forward to the rest of the training classes in this series. Thank you Brent!

Wanna join in on the fun? Here are my upcoming classes:

Fundamentals of Index Tuning: March 28th, 1-day class with optional labs for you to do on your own laptop or desktop
Mastering Query Tuning – April 1-3, 3-day in-depth class where you get a cloud VM with the big 300+GB Stack Overflow database, running workloads, and tough challenges to solve, live
Mastering Index Tuning – June 17-19
Mastering Server Tuning with Wait Stats – May 1-3, Aug 28-30 – the hardest one by far, so don’t take this one until you’ve taken both MIT and MQT.

Wanna take ’em all? Check out my Live Class Season Pass. Most of my students get this so they can take all of my classes for a year straight, and even re-take classes multiple times depending on their schedules.

Poll: what conference schedule would you prefer?

March 13, 2019

Brent Ozar

Conferences and Classes

There’s a vibrant discussion going on amongst conference attendees and organizers, and you can help.

Take this 2-question poll about your preferred session schedule, and see the results.

Pop quiz: what do these things cost?

March 12, 2019

Brent Ozar

Cloud Computing, Hardware, Licensing

It’s been a few years since we’ve stopped in for a sanity check. Quick: without searching the web for prices, put these items in order. Do it on a scratch paper, guessing how much you think each one costs, then arrange ’em.

SQL Server 2017 Standard Edition licensing for 4 cores
SQL Server 2017 Enterprise Edition licensing for 1 core
64GB of memory for a modern server (Dell r740)
256GB of memory for a modern server (Dell r740)
A mirrored pair of 4TB PCIe SSDs
One year of an 8-core, 61GB RAM, 1.9TB SSD VM in EC2 (i3.2xlarge)
One month of an 8-core, 40GB RAM Azure Managed Instance (G5 BC)
One typical day of IT employee time (say, developer, sysadmin, DBA)
One week of a team’s time (4 people)

And I’ll make it a little easier for you: group them into these buckets:

Under $2,000
$2,000-$4,000
$5,000-$10,000
Above $10K

Without searching the web, write down on scratch paper what you think they cost, and put them in order. When you’re done, check yourself in the comments.

I find that if you haven’t been shopping for this stuff recently, you might be spending a little too much time slaving over a hot Pentium 4.

How to Implement In-Memory OLTP Quickly and Easily

March 11, 2019

Brent Ozar

Architecture

When you first hear about it, SQL Server’s In-Memory OLTP sounds like a new feature that’s hard to implement, requires a lot of schema changes, and takes planning and downtime.

I’m here to tell you it’s simply not true, and you can do it in just 3 steps. Here’s how:

Read Microsoft’s guidance on how much memory you’ll need
Give your SQL Server that much
Set your max server memory setting appropriately, leaving 4GB or 10% free for the OS, whichever is higher

That’s it. That’s all there is.

Forget the feature named “In-Memory OLTP” and just put the hardware in that the feature would require anyway. You’re going to have to do it sooner or later, so just start there. Before you go writing the necessary conflict detection code, trying to rip out your unsupported cross-database queries, messing around with hash index troubleshooting, or stressing out about their statistics problems, just buy the damn RAM.

You’d be stunned at how often that simply makes the performance issues go away.

In Azure SQL DB, what does “The connection is broken and recovery is not possible” mean?

March 8, 2019

Brent Ozar

Azure SQL DB

All it really means is, “Click Execute again, and you will be fine.”

If I had a dollar for every time I saw this error message in SQL Server Management Studio:

The connection is broken and recovery is not possible.

The error says:

The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection. (Microsoft SQL Server)

That’s not what it means though. Here’s what it really means:

Click Execute again, and things will be fine.

It’s not that your network is broken, or that your IP address changed, or that there’s a firewall error. Something just happened behind the scenes where Azure decided your connection was idle and it needed to do something on the server side.

I wish Management Studio wouldn’t give in quite so easily. Just run your query again, and you’ll be fine.

Are nulls stored in a nonclustered index?

March 7, 2019

Brent Ozar

Indexing

When you index a nullable field, are the rows with nulls stored in the index? It’s easy enough to find out by creating a table with a nullable field, and then creating an index on it:

CREATE TABLE dbo.Employees
(
    ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    EmployeeName VARCHAR(50) NULL
);
GO
INSERT INTO dbo.Employees(EmployeeName) VALUES (NULL);
GO
CREATE INDEX IX_EmployeeName ON dbo.Employees (EmployeeName);
GO
/* Then get the execution plan for this: */
SELECT *
  FROM dbo.Employees
  WHERE EmployeeName IS NULL;
GO

CREATE TABLE dbo.Employees

(

ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,

EmployeeName VARCHAR(50) NULL

);

INSERT INTO dbo.Employees(EmployeeName) VALUES (NULL);

CREATE INDEX IX_EmployeeName ON dbo.Employees (EmployeeName);

/* Then get the execution plan for this: */

SELECT *

FROM dbo.Employees

WHERE EmployeeName IS NULL;

Here’s the plan:

Yep, SQL Server stores the nulls in the index. That makes sense, really, because sooner or later, you’re going to want to find the rows with nulls, and when you’ve only got a few nulls in a big table, the index can help a lot there.

To learn more about this stuff in action, watch my free 90-minute course, How to Think Like the SQL Server Engine.

What sessions do you want to see at GroupBy next month?

March 6, 2019

Brent Ozar

GroupBy Conference

GroupBy.org is a free online conference run by the community, for the community. You get to pick the sessions by voting.

Voting is open now through March 15 for the next event, happening April 11. Here are some of the sessions you can choose from:

Just Enough Database Theory for Power BI
Linux OS Fundamentals for the SQL Admin
Marketing for SQL Consultants and Business Owners
PowerShell for the SQL DBA
SQL Server Memory Internals & Troubleshooting
Think Like a Certification Exam

Go vote now. It’s totally free, no registration required.

Can deleting rows make a table…bigger?

March 5, 2019

Brent Ozar

Indexing

Michael J. Swart posted an interesting question: he had a large table with 7.5 billion rows and 5 indexes. When he deleted 10 million rows, he noticed that the indexes were getting larger, not smaller.

Here’s one way that deletes can cause a table to grow:

The rows were originally written when the database didn’t have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled
RCSI or SI was enabled, or the database was added into an Availability Group
During the deletions, a 14-byte timestamp was added to each deleted row to support RCSI/SI/AG reads

To demonstrate the growth, I took the Stack Overflow database (which doesn’t have RCSI enabled) and created a bunch of indexes on the Posts table. I checked index sizes with sp_BlitzIndex @Mode = 2 (copy/pasted into a spreadsheet, and cleaned up a little to maximize info density):

sp_BlitzIndex before

I then deleted about half of the rows:

BEGIN TRAN;
DELETE dbo.Posts WHERE Id % 2 = 0;
GO

BEGIN TRAN;

DELETE dbo.Posts WHERE Id % 2 = 0;

Amusingly, while the deletes were happening, the data file was growing to accommodate the timestamps, too! The SSMS Disk Usage Report shows the growth events – here’s just the top to illustrate:

SSMS Disk Usage Report

(Gotta love a demo where deletes make the database grow.) While the delete was running, I ran sp_BlitzIndex again. Note that the clustered index has less rows, but its size has already grown by about 1.5GB. The nonclustered indexes on AcceptedAnswerId have grown dramatically – they’re indexes on a small value that’s mostly null, so their index sizes have nearly doubled!

I don’t have to wait for the deletion to finish to prove that out, so I’ll stop the demo there. Point being: when you do big deletions on a table that was implemented before RCSI, SI, or AGs were enabled, the indexes (including the clustered) can actually grow to accommodate the addition of the version store timestamp.

Things you might take away from this:

RCSI, SI, and AGs have cost overheads you might not initially consider
After enabling RCSI, SI, or an AG, rebuild your indexes – not that it’s going to make your life better, it’s just that you can control when the page splits, object growth, fragmentation, and logged activity happens instead of waiting around for it to hit when you’re in a rush to do something
Offline index rebuilds seems to have a related effect too – Swart noted in a comment that when he tested them, they were rebuilt without the 14-byte version stamp, meaning that subsequent updates and deletes incurred the page splits and object growth
I have really strange hobbies

[Video] Demoing SQL Server 2019’s new Accelerated Database Recovery

March 4, 2019

Brent Ozar

SQL Server 2019

Wouldn’t it be nice if rollbacks just finished instantly? Wouldn’t you love for startup times to be near-zero even when SQL Server crashed just as someone in the middle of a transaction? How much would you pay for all this? (Well, I’m a little afraid to ask that, since we don’t know yet whether this is an Enterprise-only feature, but dang, I sure hope not.)

Here’s how Accelerated Database Recovery works in SQL Server 2019:

In the video, I’m using the Stack Overflow 2013 (50GB) database with this script:

/* Grow data & log files out to give us some room to work */
USE [master]
GO
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_1', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_2', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_3', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_4', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_log', SIZE = 10240000KB )
GO
/* Enable RCSI - not technically required, but more on that in another post. */
ALTER DATABASE [StackOverflow2013] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
USE StackOverflow2013;
GO
/* Rebuild the table to get the RCSI version time stamps: */
ALTER INDEX ALL ON dbo.Users REBUILD;

/* Set up an index to make our transaction slower: */
CREATE INDEX IX_Reputation_LastAccessDate 
  ON dbo.Users(Reputation, LastAccessDate);

SELECT * FROM sys.databases;



/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO
ROLLBACK

ALTER DATABASE StackOverflow2013 SET ACCELERATED_DATABASE_RECOVERY = ON;
SELECT * FROM sys.databases;

/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO

/* How much space is being used? */
SELECT TOP 100 * FROM sys.dm_tran_persistent_version_store_stats
SELECT TOP 100 * FROM sys.dm_tran_top_version_generators
SELECT TOP 100 * FROM sys.dm_tran_version_store
SELECT TOP 100 * FROM sys.dm_tran_version_store_space_usage

/* Clustered index */
SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 1, 0, 'DETAILED')

/* Nonclustered index */
SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 2, 0, 'DETAILED')

EXEC sp_persistent_version_store;

ROLLBACK;
GO

EXEC sp_persistent_version_cleanup;

/* Grow data & log files out to give us some room to work */

USE [master]

ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150