SQL Server will not start.
You open up the ERRORLOG (...which I would only do with a text editor like gVim that creates a cache of the files it opens and can safely read files that have the possibility of being written to while you are reading it...) and you see:
"Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files"
This typically happens after a long day when you should have left for home a few hours ago, btw...
In this and most cases of DBA crisis management, you will be well served by Thom LaRock's adage (that I have on my cube wall at work) - Keep Calm and Blame the Network.
If that doesn't work, make sure no one is around and let the technology really know how you feel.
OK, know this - the solution is extremely simple. You told TEMPDB how big to become when SQL Server starts and there isn't enough space in the path you told it to be created in. Therefore, you simply need to tell it to now create smaller TEMPDB files.
Ah! - but the SQL Service won't start, Einstein.
Yes it will, from the Command Prompt using the relevant Database Engine Service Startup Options.
Open two Command Prompt windows (as Admin or a different Domain User as necessary depending on your OS or your security access controls).
In one window have this command ready to go and run it immediately AFTER you start SQL in our second Command Prompt window (this is important as we will be starting SQL in single user mode and if you let something else grab that one and only connection you'll be out of luck):
sqlcmd -E
OK, in our second window navigate to the BINN dir where sqlservr.exe resides and type the following. Press ENTER and then immediately go back to the other window where we have SQLCMD teed up and press ENTER on the SQLCMD exe.
sqlservr.exe -c -f -m
Once you have a SQLCMD session, simply use the ALTER statement to reduce the size of your TEMPDB files. When done, run the "shutdown" statement from SQLCMD and restart SQL from Services.
