The TEXTSIZE trap: how TDS silently truncates your binary data in Rails

During a legacy data migration, imported images kept rendering as broken files. Everything looked fine in the logs. No errors, no warnings. It took me some back and forth to finally discover that TDS was quietly truncating varbinary(max) columns on the way out. One SQL command fixed it.

client.execute("SET TEXTSIZE 2147483647").do

TDS (Tabular Data Stream) is the protocol SQL Server uses to send data between the database and your application. tiny_tds communicates with SQL Server over TDS via FreeTDS, an open-source implementation of the protocol.

What TEXTSIZE controls

TEXTSIZE is a session-level SQL Server setting that caps how many bytes the server returns for large object columns. It applies to varbinary(max), varchar(max), nvarchar(max), and the older text, ntext, and image types.

It does not affect writes. Data going into the database is stored in full. The truncation happens on the way out, before the data ever reaches your Ruby code, which is why nothing in your application raises an error.

Why the default is confusing

The frustrating part is that the default TEXTSIZE depends entirely on which layer of the stack you ask.

SQL Server’s internal session default is 4,096 bytes, though most database drivers override this automatically. SSMS, ODBC, and .NET’s SqlClient all raise the limit on connection. Running SELECT @@TEXTSIZE on a fresh session without any client override returns 4096.

FreeTDS itself doesn’t reliably raise TEXTSIZE. What you actually get depends on freetds.conf. Without a text size directive, you fall back to SQL Server’s 4KB default. With one, you get whatever it specifies. Some FreeTDS example configurations floating around include text size = 64512, which silently caps your data at about 63KB.

The activerecord-sqlserver-adapter cuts through all of this by running SET TEXTSIZE 2147483647 in its configure_connection method on every new connection. If you’re using the adapter, this is handled for you.

If you’re using tiny_tds directly, nothing sets it for you. The TinyTds::Client.new constructor has no textsize parameter. You have to set it yourself, right after connecting.

The fix for standalone tiny_tds

client = TinyTds::Client.new(
  username: "user",
  password: "password",
  host: "your-sql-server",
  database: "your_db"
)

# Must run this before any query on large object columns
client.execute("SET TEXTSIZE 2147483647").do

The value 2147483647 is 2^31 - 1, the maximum signed 32-bit integer, which SQL Server treats as roughly 2GB per large object column. You may see SET TEXTSIZE -1 mentioned as an unlimited alternative, but this behavior is inconsistent across drivers and FreeTDS versions. The explicit integer is safer.

If you have a wrapper class around your migration client, set it in the initializer so you can’t forget it:

class LegacyClient
  def initialize
    @client = TinyTds::Client.new(connection_options)
    @client.execute("SET TEXTSIZE 2147483647").do
  end

  def query(sql)
    @client.execute(sql)
  end
end

What TEXTSIZE won’t fix

If your migration is failing on large binary inserts rather than selects, TEXTSIZE is not the problem. It only governs what the server sends back. Very large inline binary values in SQL statements can hit FreeTDS packet and buffer limits instead. For those cases, parameterized queries are the right path.

What to check if truncation is still happening

You can confirm the current setting on any active connection:

client.execute("SELECT @@TEXTSIZE").each { |row| puts row[""] }

If that returns 4096, the override hasn’t taken effect. Then check these in order:

• Look for a text size line in freetds.conf that might be capping the value
• Verify you’re setting TEXTSIZE before the query, not after
• Remember that TEXTSIZE persists for the lifetime of the session, so a previous query on the same pooled connection could have lowered it
• Remember that nvarchar(max) uses UTF-16 encoding (typically 2 bytes per character), so a 4KB limit returns roughly 2,048 characters, not 4,096

The TEXTSIZE default is a reasonable historical choice for applications that don’t need large object data. For migrations dealing with binary files, setting it explicitly is a one-liner. Forgetting it might cost you hours.