Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Recover From a Runaway TempDB Filling the Entire Drive

- 30.08.26 | 31.08.26 - ErcanOPAK

🗄️ TempDB at 100% = Whole Server at Risk

TempDB filling its drive doesn’t just fail the one query causing it — every other database on that SQL Server instance shares the same TempDB, so a single runaway sort, hash join, or long-open transaction can bring your entire server to a halt.

🔍 Find What’s Actually Consuming TempDB Right Now

-- Which sessions are using the most TempDB space right now
SELECT
    s.session_id,
    r.status,
    (t.internal_objects_alloc_page_count + t.user_objects_alloc_page_count) * 8 / 1024 AS mb_used,
    r.command,
    t2.text AS running_query
FROM sys.dm_db_task_space_usage t
JOIN sys.dm_exec_sessions s ON s.session_id = t.session_id
JOIN sys.dm_exec_requests r ON r.session_id = t.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t2
ORDER BY mb_used DESC;

🐞 The Usual Culprits

1. A query doing a huge sort or hash join without a helpful
   index - the intermediate results spill to TempDB.
2. A long-running or forgotten-open transaction under snapshot/
   RCSI isolation - SQL Server keeps a version store in TempDB
   for as long as that old transaction stays open, even if it's
   not touching the affected rows anymore.
3. Excessive use of table variables or temp tables (#temp) with
   very large row counts inside a loop.

✅ Immediate Relief

  • Identify the worst offending session from the query above, and if it’s safe to do so, KILL it — check with the team first if this is shared production.
  • Check for old open transactions specifically: SELECT * FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC.

🛑 Kill the Offending Session

KILL 62; -- replace 62 with the actual session_id from the query above
-- This rolls back that session's transaction, which may take a
-- while itself if it had done a lot of work - be patient, don't
-- kill -9 the whole SQL Server process instead.

🛡️ Prevent the Next One

  • Size TempDB proportionally to your workload up front, and split it across multiple equally-sized data files (one per CPU core, up to about 8) to reduce contention.
  • Set TempDB’s autogrowth to a fixed MB amount, not a percentage — percentage growth gets slower and slower as the file grows.
  • Add monitoring/alerting on TempDB free space specifically, separate from your general disk-space alerts, since it fills far faster than a normal data file.

TempDB is shared infrastructure for the whole instance — a runaway query in one database can starve every other database on the box, which is exactly why it deserves its own dedicated monitoring.

— Database Engineer

Related posts:

SQL Server: Fix a Partitioned Table Query Silently Scanning Every Partition Because the Predicate Co...

SQL Server: Fix a Partitioned Table Query Silently Scanning Every Partition Because the Predicate Co...

SQL: Use Indexes to Speed Up Slow Queries

SQL: Use Indexes to Speed Up Slow Queries

SQL: Use WITH RECURSIVE to Query Hierarchical Data

SQL: Use WITH RECURSIVE to Query Hierarchical Data

Views: 11

Post navigation

Git: Remove a Committed Secret From History Before It’s Too Late
The AI Prompt That Turns a Pile of Receipts Into a Categorized Monthly Budget in Minutes

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (607)
  • Average of all values in a column that are not zero in SQL (569)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com