<!-- mobian-agent-page publisher="dailydev" canonical="https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a" -->

---
title: Killing a tempdb killer – Part 1 | daily.dev
description: A SQL Server consultant describes a monitoring system built to catch tempdb growth problems before they fill a drive. A view called DBA.TempDB_Risks exposes...
canonical: https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a
twitter:card: summary_large_image
twitter:site: @dailydotdev
og:type: website
og:site_name: daily.dev
og:title: Killing a tempdb killer – Part 1 | daily.dev
og:description: A SQL Server consultant describes a monitoring system built to catch tempdb growth problems before they fill a drive. A view called DBA.TempDB_Risks exposes...
og:url: https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a
og:image: https://api.daily.dev/og/posts/S3cR6bt7a.png
og:image:alt: Killing a tempdb killer – Part 1
og:image:width: 1200
og:image:height: 630
og:locale: en
---

> ## Documentation Index
> Fetch the complete documentation index at: https://daily.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Killing a tempdb killer – Part 1

**[SQLBlog.org \(Aaron Bertrand\)](https://daily.dev/sources/sqlblog)** · 63 min read · 1 upvotes · 0 comments

## Summary

A SQL Server consultant describes a monitoring system built to catch tempdb growth problems before they fill a drive. A view called DBA.TempDB_Risks exposes space, configuration drift, and autogrow risks, while a stored procedure, DBA.TempDB_Collect, runs on ten-second boundaries and starts detailed session logging once free space drops below 50%. Alerts escalate at 50%, 75%, and under 10% free. With guardrails in place, the system can automatically kill a session responsible for overwhelming tempdb data or log usage, such as one representing at least 75% of used tempdb data space for five minutes, while preserving evidence for root cause analysis.

## Full article

daily.dev links to this article rather than hosting it. Read it at the original source: <https://sqlblog.org/2026/11/16/to-kill-a-tempdb-killer-part-1>

## Questions this post answers

### How can I automatically detect and kill a session that's filling up SQL Server tempdb?

A monitoring system can track tempdb free space and nominate a session for automatic termination once it dominates usage. One rule kills a session that dominates attributed tempdb data usage across consecutive snapshots and either grows by at least 128 pages (1 MB) or sits idle with outstanding allocations, subject to guardrails like excluding backups, restores, index operations, and DBCC.

_Developers hardening SQL Server against runaway tempdb usage track implementation patterns like this on daily.dev._

### What's a good early warning threshold for SQL Server tempdb free space before it fills up?

A tiered alerting approach uses 50% free space as the initial 'things are getting bad' threshold, 25% free as a 'hair on fire' alert, and under 10% free (or a drive crossing its 10% free-space threshold, or a failing next autogrow) as the most severe 'whole house is on fire' alert, triggering detailed session logging at the first tier.

_Teams tuning database alert thresholds compare approaches like this one on daily.dev._

### What is the recommended number of tempdb data files in SQL Server based on visible schedulers?

Initial guidance caps the data file count at 8, using the count of visible online schedulers when that number is 8 or fewer. A configuration check compares the actual data file count against this guidance, derived from sys.dm_os_schedulers rows with status 'VISIBLE ONLINE', and flags a mismatch as a warning.

_DBAs configuring tempdb file counts reference guidance like this while reading on daily.dev._

## Similar posts on daily.dev

- [SQL Server Blocking at 2 a.m.: You Are the On-Call DBA](https://daily.dev/posts/sql-server-blocking-at-2-a-m-you-are-the-on-call-dba-j632it1qh) · SQL Authority · 0 upvotes · 0 comments
- [My C: drive kept running out of space, and I finally found how to stop it for good](https://daily.dev/posts/my-c-drive-kept-running-out-of-space-and-i-finally-found-how-to-stop-it-for-good-wqal0q8bk) · XDA Developers · 0 upvotes · 0 comments
- [\[Video\] Office Hours: Open Q&A for Microsoft Databases](https://daily.dev/posts/video-office-hours-open-q-a-for-microsoft-databases-5qyywfnuf) · Brent Ozar · 0 upvotes · 0 comments
- [Why do you have so many housekeeping jobs?](https://daily.dev/posts/why-do-you-have-so-many-housekeeping-jobs--q5kep9r0k) · SQLBlog.org \(Aaron Bertrand\) · 0 upvotes · 0 comments
- [Monitoring the Health of KurrentDB Clusters Part 1](https://daily.dev/posts/monitoring-the-health-of-kurrentdb-clusters-part-1-bbs78ahtv) · Event Store · 0 upvotes · 0 comments

---

Tags: [#database](https://daily.dev/tags/database), [#microsoft-sql-server](https://daily.dev/tags/microsoft-sql-server)

[View this post on daily.dev](https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a)

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]}
{"@context":"https://schema.org","@type":"TechArticle","headline":"Killing a tempdb killer – Part 1","url":"https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a"},"datePublished":"2026-11-16T07:01:10.905Z","dateModified":"2026-11-16T07:02:36.660Z","description":"A SQL Server consultant describes a monitoring system built to catch tempdb growth problems before they fill a drive. A view called DBA.TempDB_Risks exposes...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/e7c397f525eb10ae6c4ac5635f077570?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/e7c397f525eb10ae6c4ac5635f077570?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"SQLBlog.org (Aaron Bertrand)","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"SQLBlog.org (Aaron Bertrand)","logo":"https://media.daily.dev/image/upload/s--dP89h5-p--/f_auto,q_auto/v1791720356/logos/sqlblog?_a=BAMAMicg0","url":"https://daily.dev/sources/sqlblog"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":1},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"database,microsoft-sql-server","timeRequired":"PT63M"}
{"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"SQLBlog.org (Aaron Bertrand)","item":"https://daily.dev/sources/sqlblog"},{"@type":"ListItem","position":3,"name":"Killing a tempdb killer – Part 1"}]}
{"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/killing-a-tempdb-killer-part-1-s3cr6bt7a#faq","mainEntity":[{"@type":"Question","name":"How can I automatically detect and kill a session that's filling up SQL Server tempdb?","acceptedAnswer":{"@type":"Answer","text":"A monitoring system can track tempdb free space and nominate a session for automatic termination once it dominates usage. One rule kills a session that dominates attributed tempdb data usage across consecutive snapshots and either grows by at least 128 pages (1 MB) or sits idle with outstanding allocations, subject to guardrails like excluding backups, restores, index operations, and DBCC. Developers hardening SQL Server against runaway tempdb usage track implementation patterns like this on daily.dev."}},{"@type":"Question","name":"What's a good early warning threshold for SQL Server tempdb free space before it fills up?","acceptedAnswer":{"@type":"Answer","text":"A tiered alerting approach uses 50% free space as the initial 'things are getting bad' threshold, 25% free as a 'hair on fire' alert, and under 10% free (or a drive crossing its 10% free-space threshold, or a failing next autogrow) as the most severe 'whole house is on fire' alert, triggering detailed session logging at the first tier. Teams tuning database alert thresholds compare approaches like this one on daily.dev."}},{"@type":"Question","name":"What is the recommended number of tempdb data files in SQL Server based on visible schedulers?","acceptedAnswer":{"@type":"Answer","text":"Initial guidance caps the data file count at 8, using the count of visible online schedulers when that number is 8 or fewer. A configuration check compares the actual data file count against this guidance, derived from sys.dm_os_schedulers rows with status 'VISIBLE ONLINE', and flags a mismatch as a warning. DBAs configuring tempdb file counts reference guidance like this while reading on daily.dev."}}]}
```

