When TempDB Grows “Up and to the Right”: How RCSI and Open Transactions Quietly Break SQL Server

One of the most dangerous SQL Server problems is the one you don’t notice until the disk is full and production is down. TempDB is especially good at failing this way—quietly growing in the background until it suddenly becomes everyone’s problem.

Recently, we were reviewing the TempDB Allocation History Report, and it perfectly illustrated an issue we’re seeing more often as READ COMMITTED Snapshot Isolation (RCSI) becomes more widely adopted. RCSI is usually the right choice, but when something goes wrong, TempDB can explode in size—and most teams don’t realize why until it’s too late.

Why the TempDB Allocation History Report Matters

The TempDB Allocation History Report is most valuable on production systems with real workload. Test servers rarely show anything interesting because they don’t experience long-running transactions, heavy concurrency, or sustained user activity.

On production servers, however, this report tells a story. It shows where TempDB space is being used over time, including:

• Version store
• User objects
• Internal objects
• Temporary tables

When TempDB starts growing unexpectedly, this report lets you visually identify the root cause instead of guessing.

RCSI Is Not the Enemy

Let’s be clear: RCSI is not a bad feature. In fact, we recommend it for roughly 99% of production SQL Server environments. It dramatically reduces blocking by allowing readers to access row versions instead of waiting on locks.

But RCSI has a cost, and that cost is paid in TempDB.

When RCSI is enabled, SQL Server stores older row versions in the version store, which lives in TempDB. Under normal conditions, those versions are cleaned up automatically once transactions complete.

The problem arises when they don’t complete.

Open Transactions and the Version Store

An open transaction under RCSI prevents the version store from cleaning up old row versions. As long as that transaction remains open, SQL Server must retain every row version needed to support consistent reads.

This is where things go bad.

We’ve seen clients where an application left a transaction open for hours—or even days—while other activity continued normally. Every update, delete, or insert during that time created more versions. The version store kept growing, and TempDB followed right along.

In one real-world case, a client ended up with a four-terabyte TempDB on a system where none of the user databases were even close to that size. The culprit was a single open transaction combined with RCSI.

What the Chart Would Have Told Us

When you look at the TempDB Allocation History Report in a scenario like this, the problem reveals itself instantly.

If the chart is almost entirely version store usage, you know the issue is RCSI-related. No guesswork. No digging through dozens of queries. Just a clear signal pointing to open transactions.

What you’ll often see is an “up and to the right” growth pattern. That’s great when you’re looking at sales numbers. It’s terrible when you’re looking at TempDB size.

TempDB should fluctuate. It should grow and shrink. When it only grows—and never comes back down—you have a cleanup problem.

Connecting the Dots with Open Transactions

This is where pairing reports becomes powerful.

When TempDB version store growth is the issue, the Open Transactions Report almost always confirms it. You’ll typically find:

• Long-running transactions
• Sleeping sessions holding transactions open
• Application code that never commits or rolls back properly

As long as those transactions remain open, TempDB will continue to grow. Backups won’t help. Restarting jobs won’t help. The only real fix is identifying and resolving the open transaction.

Why Most Teams Miss This

Most IT teams aren’t watching TempDB closely enough. Even fewer understand how RCSI, version store cleanup, and open transactions interact.

By the time TempDB fills the drive, the damage is already done. The server may stop responding, applications fail, and suddenly everyone is asking, “How did this happen?”

The answer is almost always: it happened slowly, quietly, and without proper monitoring.

How We Catch This Before It Becomes a Crisis

At Stedman Solutions, this is exactly the type of issue we prevent with proactive SQL Server monitoring and DBA-managed services.

Using Database Health Monitor, we continuously track TempDB usage, version store growth, and open transactions. When TempDB starts trending “up and to the right,” we investigate immediately—long before the disk fills up.

This kind of visibility allows us to:

• Identify RCSI-related version store bloat early
• Pinpoint open transactions causing cleanup delays
• Work with development teams to fix the root cause
• Prevent outages instead of reacting to them

You can learn more about Database Health Monitor at http://DatabaseHealth.com

The Bigger Picture: SQL Server Managed Services

TempDB explosions are rarely isolated incidents. They’re symptoms of systems that aren’t being actively monitored and maintained.

With SQL Server DBA Managed Services from Stedman Solutions, you get:

• True SQL Server specialists with decades of experience
• Continuous monitoring and alerting
• Immediate remediation when problems arise
• Mentoring and knowledge transfer so your team gets stronger over time

If TempDB issues, open transactions, or RCSI side effects sound familiar, it’s worth having a conversation before the next outage.

Learn more about our Managed Services here: https://stedmansolutions.com/managed-services/

Or contact us directly to talk through your environment: https://stedmansolutions.com/contact-us/