A SQL Server running out of room fails in several different ways depending on exactly which resource ran out: a full data file stops inserts, a full log file freezes the whole database, a full disk volume takes down everything on it, and a database quietly approaching a configured file size limit gives no warning at all until it hits the wall. Ten scripts on this site cover the full picture, from disk volumes down to individual filegroups, from a point-in-time snapshot to a real growth-rate forecast.
This post is the map. It groups them by the question they answer, and lays out a sensible order for a routine capacity review rather than checking each in isolation. (For a plain list of what databases exist on the server in the first place, that’s a different question answered by Database Inventory in the Server Inventory pillar. This cluster assumes you already know what’s here and answers how much room it has.)
Why Storage and Capacity Matters
- Different resources fail differently: a full data file errors individual inserts, a full log file freezes every write in that database, a full disk volume can take down every database hosted on it
- Free space at the volume, database, and filegroup level are three different questions, a comfortable database-level total can hide a nearly-full filegroup, and a comfortable disk can still have a database configured with a tight max file size
- A snapshot tells you where things stand right now, a trend tells you when a real problem is coming, both matter and neither replaces the other
- Autogrowth is a safety mechanism, not a capacity strategy, frequent growth events during business hours cost latency even when they succeed
Start Here
The Scripts, Grouped by What They Answer
💾 Is there room right now?
- Disk Space, free and used space per OS volume hosting database files, queried through T-SQL alone, no RDP access needed
- Database Free Space Summary, allocated, used and free space per database, split into data and log, ordered by tightest free space first
- Database Sizes and Free Space, the same data and log split, ordered biggest database first, an inventory view rather than a triage view
- Filegroup Space, the same question one level deeper, since autogrow acts per file within a filegroup, not at the database level
📊 What is actually using the space?
- Table Sizes, row counts and space per table, so the biggest consumers inside a database are named rather than guessed
- Compression Candidates, tables and indexes where page compression would reclaim the most, with the estimated saving
- Database Files Detail, per-file path, size, max size and growth settings, the configuration behind every number the other scripts report
- Database Snapshot Inventory, snapshots still on disk, which quietly consume space long after whoever created them has forgotten
📈 Is it trending toward a problem?
- Database Growth Risk and Forecast, growth rate per database projected forward, so capacity is a date rather than a feeling
- Autogrowth History, when files actually grew, which exposes growth firing repeatedly during business hours
🏭 Is tempdb the problem?
- TempDB Configuration, file count, sizes and growth settings against what they should be for the core count on this server
- TempDB Usage and File Balance, which sessions are consuming tempdb and whether the files are filling evenly
- TempDB Hotspots, the allocation contention that shows up as PAGELATCH waits rather than as a space problem
📜 Is the transaction log the problem?
- Log Reuse Waits, the reason a log will not truncate, which is the first question when a log grows and will not stop
- Transaction Log Size and Usage, how full each log actually is, as opposed to how large the file has become
- VLF Counts, virtual log file fragmentation, which slows recovery and startup long before it shows as a space issue
How They Fit Together
A sensible order for a routine capacity review:
- Start at the volume level with Disk Space, the broadest check and the one that needs no database-level setup at all
- Drop to the database level with Database Free Space Summary (or Database Sizes and Free Space for an inventory-first view) to see which specific databases are tight
- Go one level deeper with Filegroup Space when a database-level number looks comfortable but autogrow is still failing, or when the database uses more than one filegroup
- Check the configuration behind the numbers with Database Files Detail, growth settings and max size explain why a file behaves the way it does
- Check the trend, not just the snapshot with Database Growth Risk and Forecast and Autogrowth History, a comfortable number today can still be on a fast trajectory
- If the log specifically is the problem, start with Log Reuse Waits to find the actual blocking reason before reaching for Transaction Log Size and Usage or VLF Counts
Best Practices Across the Series
- Check free space at all three levels (volume, database, filegroup) before concluding a database is safe, each can hide a problem the others don’t show
- Treat
UNLIMITEDgrowth settings as “check the disk,” not as “no risk,” the real ceiling is still the volume underneath - Set up growth-rate collection early, even before a capacity conversation comes up, so a real forecast is available when someone asks “when will we run out”
- Fix a log that won’t reuse space at the actual cause (
Log Reuse Waits) before shrinking it, a one-off shrink without fixing the cause just grows back
Frequently Asked Questions
Why does a database show plenty of free space but still throw an autogrow error?
Free space at the database level is a total. Autogrow acts per file inside a filegroup, so a database can report gigabytes free while the one file being written to has hit its MAXSIZE or the volume underneath is full. Check filegroup and per-file numbers, not just the database total.
Do I need RDP or xp_cmdshell to check disk space?
No. The Disk Space script reads volume-level free and used space through T-SQL alone, using the dynamic management views SQL Server already exposes. That matters because it works on servers where you have a SQL login but no Windows access at all.
Is shrinking a database file ever the right answer?
Rarely, and almost never on a schedule. Shrinking fragments indexes and the space usually grows straight back, so you pay the fragmentation cost repeatedly. It is defensible as a one-off after a genuine large deletion, followed by an index rebuild.
What is a sensible autogrowth setting?
A fixed size rather than a percentage, large enough that growth is infrequent but small enough that a single growth does not stall writes. Percentage growth compounds: it gets slower and larger exactly as the database gets bigger, which is the wrong way round.
Why is my transaction log full when the database has free space?
The log is a separate file with its own rules. It cannot truncate while something needs the records, an open transaction, a replication or availability group backlog, or simply no log backup on a database in FULL recovery. Check the log reuse wait first, not the file size.
See Also
This pillar is part of DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
Running out of room is never really one problem, it’s several different questions depending on which resource and which level you’re looking at: the disk, the database, the filegroup, and whether the trend is getting worse. These ten scripts answer each of those separately, because a full disk, a full log, and a database quietly approaching a configured limit all need a different response.
Start at the volume level if you haven’t run a capacity review recently, then follow the chain down into whichever level turns up a real finding.
Leave a Reply