Fix SQL Server 9002 disk full: shrink log file

You're probably reading this because SQL Server stopped working and threw error 9002. The drive is full. I've seen this happen more often than it should — usually because the transaction log file (the .ldf) has grown out of control and nobody noticed until the database went down. Let's get you back up.

First: free space immediately

You need to shrink that log file now. But be careful — if the database is in FULL recovery model (which it probably is for production), a simple SHRINKFILE command won't do much. You have to truncate the log first.

Open SQL Server Management Studio (SSMS).
Connect to your server.
Open a new query window for the database that's in trouble.
Run this command — it switches the database to SIMPLE recovery model, which forces a log truncation:
```
ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;
```
After you run it, the query should finish quickly. You'll see "Commands completed successfully."
Now shrink the log file:
```
DBCC SHRINKFILE (YourDatabaseName_log, 1);
```
Find the logical log file name first by running SELECT name FROM sys.database_files WHERE type_desc = 'LOG';. Replace YourDatabaseName_log with that name. The target size is 1 MB — you can adjust that up if you know what size you need.
After shrinking, switch back to FULL recovery (assuming you need point-in-time recovery):
```
ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;
```

That should free up the disk space immediately. If the drive still shows full, refresh Windows Explorer or run EXEC xp_fixeddrives; in a query window to see updated free space.

Why this worked

The transaction log file holds committed transactions that haven't been backed up yet. In FULL recovery model, no transaction is ever removed from the log until a log backup runs. If you never take log backups — or your backup job broke weeks ago — that file grows until it fills the disk.

Switching to SIMPLE recovery tells SQL Server "I don't need point-in-time recovery anymore" and it immediately truncates the inactive part of the log. That's why the shrink works right after. It's a blunt fix, but it gets you back online.

Less common variations of the same problem

1. The log file won't shrink because of a long-running transaction

Sometimes you switch to SIMPLE and the shrink command still doesn't free space. That means there's an open transaction — a query that's been running for hours, or a transaction that was started but never committed or rolled back.

Run this to find the culprit:

DBCC OPENTRAN;

If you see a transaction with a long start time, you have two choices:

Kill the session (using KILL with the SPID shown). This rolls back the transaction — could take a while if it's huge.
Or restart the SQL Server service. That forces all transactions to roll back. Not ideal in production, but sometimes it's the only way.

After the transaction is gone, run the shrink again.

2. The data file (.mdf) is the one that's full, not the log

Check which file is actually full. Run EXEC sp_helpdb 'YourDatabaseName'; and look at the size column for both files. If the .mdf is huge, the fix is different:

Add a new data file on another drive: ALTER DATABASE [YourDB] ADD FILE (NAME = 'NewDataFile', FILENAME = 'D:\Data\YourDB.ndf', SIZE = 10GB);
Or run index maintenance to reclaim space inside the existing file — but that's a slow process.

3. The tempdb database filled the disk

tempdb can grow uncontrollably if a query creates a huge sort or spool. Restarting SQL Server resets tempdb to its original size. But if it keeps growing, you need to find the rogue query. Use SELECT * FROM sys.dm_exec_query_resource_semaphores; to see who's eating tempdb space.

Prevention: keep this from coming back

You don't want to be here again. Here's what to do after you've recovered:

Set up log backups. If you need FULL recovery model (for point-in-time restore), schedule a transaction log backup every 15–30 minutes. Use this T-SQL for a quick backup:
```
BACKUP LOG [YourDatabaseName] TO DISK = 'D:\Backups\YourDB_log.trn' WITH INIT;
```
Set up a SQL Agent job to run that every 15 minutes.
Cap the log file size. This prevents it from eating all disk space even if backups fail. Set a maximum size:
```
ALTER DATABASE [YourDatabaseName] MODIFY FILE (NAME = 'YourDB_log', MAXSIZE = 5GB);
```
Pick a size that's reasonable for your workload — I usually set it to 20% of the data file size.
Monitor disk space. Set up a simple alert. Run EXEC xp_fixeddrives; in a job every hour, and email yourself if free space drops below 10%.
If you don't need point-in-time recovery, keep the database in SIMPLE recovery model. The log will still grow under heavy transactions, but it'll truncate automatically on checkpoints. That's what I use for dev and staging databases.

One last thing: if this is a repeat problem on a production server, schedule a weekly index rebuild and update statistics. Fragmented indexes cause bigger sorts, which make the log grow faster. Keep your house in order.