---
title: 'The TEXTSIZE trap: how TDS silently truncates your binary data in Rails'
teaser: SQL Server has a session-level setting that silently truncates your binary
  data before it ever reaches Ruby. It defaults to 4KB. Most drivers fix it automatically.
  tiny_tds does not.
tags: sql,ruby on rails,database
author: Trésor Bireke
published_on: 2026-05-07
---

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.

```ruby
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`](https://github.com/rails-sqlserver/tiny_tds) communicates with SQL Server over TDS via [FreeTDS](https://www.freetds.org/), an open-source implementation of the protocol.

## What TEXTSIZE controls

[`TEXTSIZE`](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-textsize-transact-sql) 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`](https://github.com/rails-sqlserver/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

```ruby
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:

```ruby
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:

```ruby
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.