Mark Gilbert's Blog

Science and technology, served light and fluffy.

He just cut me off! SSMS and Display Limits in the Query Grid

I’m using NLog in my current project to record errors, tracing, and other information while my application is running.  Some of the log messages, particularly the errors, can get quite long – thousands of kilobytes – because part of what I’m recording is the raw XML being passed into the app.

I have NLog configured to both email me when an error occurs and record the details to a table in SQL Server 2008.  I was running a load test recently, and noticed a few errors being recorded.  When I looked at the "Message" field in the table, I found that the message was being truncated – in fact, it was cutting the raw XML off.  Since I use this for reproducing the error, figuring out why it was being truncated was fairly important.

As I investigated more deeply, I found several things that were odd about this:

  1. First, I checked the "Message" field in the table to make sure it was large enough to store everything.  I confirmed that it was defined as NVARCHAR(max) field, so there should have been ample room.
  2. Next, I looked at the version of the error that got emailed to me.  That version had the complete message.
  3. I next added a "File" target to my NLog.config file.  I wanted to see what would get recorded if I wrote the error message out to a text file.  That version also had the complete message.
  4. Finally, the messages were always being truncated at 43,679 bytes.

So, it seemed that I was only having problems writing NLog error messages to SQL Server.  Perhaps there was something wonky with the "database" NLog target source.  I dug that up on the internets, and didn’t see anything that should be truncating the messages, let alone at 43,679 bytes.

I next decided to try inserting a large message directly into SQL Server, via some quickly-crafted C# logic.  I wanted to see if I could eliminate NLog as being the culprit here.  I inserted 30KB and 40KB messages, and those worked fine – I could insert them without errors, and I could get them out of SQL Server Management Studio (SSMS) completely intact.  When I tried a 50KB file, though, that failed in the same way – no errors on the insert, but when I queried the table in SSMS and copied the message to a text file, it was truncated.

What. The. Heck?!?

I did some more digging on the internets, this time for "sql server truncate" and other variants, and came across this post on  From Jim McLeod’s answer:

"Yep – you got it. There’s a setting in Management Studio – Tools, Options, Query Results, SQL Server, Results To Grid, Maximum Characters Retrieved – Non XML Data, which defaults to 64KB."

So SSMS is limiting the text it’s going to display in the grid.  Ok, easy enough, I just have to increase this default limit.  I found the option:

SSMS Options

And tried to change it to 100KB.  The dialog looked like it saved it, but when I went back in later it had been reverted to 65KB.  I tried the spinner controls, and it wouldn’t let me go above 65KB.

SSMS – why do you taunt me so?

Since Management Studio won’t let me view this via a query, perhaps I just need to extract it.  I first tried extracting this to a flat file, but that choked because the Message field was an NVARCHAR, and there was a problem with the character set.  I next tried exporting to Excel, but that choked because individual cells in Excel can’t hold more than 32KB.  In the end, I just wrote a little app in C# that extracts the message and saves it to a text file.

I’m going to officially write this up as a case of "tool-fail", if only because it was failing silently.  Microsoft: At least tell me that the query I ran returned text results that were greater than 65KB, and would be truncated.  If you really want brownie points, tell me where this limit is defined in the Tools dialog, and even better LET ME INCREASE IT.

Ok, I’m done.

September 11, 2013 - Posted by | SQL Server

Sorry, the comment form is closed at this time.

%d bloggers like this: