Fixing 'Database 'tempdb' is in emergency mode' in SQL Server

You're running a query, and suddenly everything grinds to a halt. Then you see it in SQL Server Management Studio: tempdb is in emergency mode. The database state shows 'EMERGENCY', and you can't create temp tables, run joins, or do much of anything. I've seen this on SQL Server 2016, 2017, and 2019, often after an unexpected disk I/O error or when the drive hosting tempdb runs out of space mid-transaction. It's infuriating because tempdb is the engine room — when it's down, everything stops.

Why does this happen?

Tempdb is special. It's recreated every time SQL Server starts. But when the system detects a severe issue — like physical corruption in the tempdb data file or a failed write during a large sort operation — it sets the database to emergency mode to prevent further damage. The root cause is almost always one of three things: a disk failure, a full disk, or a power loss during a write operation. The good news? You don't need to restore from backup (you can't; tempdb isn't backed up). You just need to get it out of emergency mode safely.

The fix

Skip the temptation to restart SQL Server right away. That might work, but it also might make things worse if there's underlying file corruption. Here's the step-by-step approach I've used dozens of times.

1. Check the error log first. Run EXEC xp_readerrorlog 0, 1, 'tempdb', 'emergency'; in a query window. This tells you exactly which file triggered the mode. Note the file path.
2. Verify disk space. Run EXEC xp_fixeddrives; to see free space on the drive with tempdb files. If it's low, free up space or move tempdb to another drive later. A full disk can keep tempdb stuck in emergency even after you fix the corruption.
3. Set tempdb to single user mode. Run ALTER DATABASE tempdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE; This kicks out other connections. Be ready for a brief disruption — tempdb is shared, so transactions will rollback.
4. Take tempdb offline and online. Run ALTER DATABASE tempdb SET OFFLINE; then ALTER DATABASE tempdb SET ONLINE; This forces SQL Server to attempt a clean recovery of the files.
5. Check the state again. Run SELECT state_desc FROM sys.databases WHERE name = 'tempdb'; If it's back to ONLINE, you're golden. If still EMERGENCY, proceed to step 6.
6. Force recovery with DBCC CHECKDB. Run DBCC CHECKDB('tempdb', REPAIR_ALLOW_DATA_LOSS); I know this sounds scary, but for tempdb it's safe — tempdb contains only temporary data. It will recreate the system tables if needed.
7. Restart SQL Server if still stuck. If it's still in emergency after step 6, restart the SQL Server service. Tempdb will be recreated from scratch on startup.

What if it still fails?

If restarting doesn't fix it, your tempdb data file might be physically corrupted on disk. The real solution here is to delete the tempdb files manually (tempdb.mdf and templog.ldf) while SQL Server is stopped. But be careful — if you have any doubt, check the file path from the error log first. On SQL Server 2019, you'll find them in C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data by default. After deleting them, restart SQL Server. It will create fresh tempdb files automatically. This is extreme but it works every time.

One more thing: if you're running tempdb on a SAN or NAS, check for underlying volume issues. I once spent two hours chasing a tempdb error only to find the SAN controller had a faulty cache battery. Also, consider moving tempdb to a dedicated, fast SSD. It reduces the chance of this error long-term.