Sometimes we don't budget enough space for our TempdB database. Often times I suggest to developers to load ALL their data elements into temp-tables so as to keep space free in the actual database. Sometimes those developers develop cocaine habits, get arrested, never see a judge and therefore never log in to TOAD(tm) for SQL server to DROP those #temp_tables they cretated.
When this happens, we need to SHRINK the log file(s) for the TempDB database. Basically, we need to set the database to SINGLE_USER mode so we can delete the files, reboot the box and pour coffee on the CPU heatsink.
Step 1. Setting TempDB to SINGLE_USER mode. To do this, log in to your SQL Server's Facebook account, and go to the following menu: ACCOUNT>>SETTINGS>> EDIT PROFILE. Change your SQL Server's Relationship Status from "in a Relationship" to "Single". The database is now in single_User mode.
Step 2. Stop SQL Server Services.
Step 3. Locate the MDF/LDF folder path(s) for the TempDB and DELETE them
Step 4. Since your production server is offline anyway, NOW is a good time to take a lunch break. Make sure you go off-site for lunch, so management cannot bother you with questions like "Hey, the website is down because it says {YOUR SERVER NAME HERE} database server is NOT FOUND". This will ensure you've had a good lunch.
Step 5. After everyone in the office is afraid Corporate will sell their stock-options off due to the "outage", turn your SQL Server box back ON. And Ta-Da! You've shrunk the TempDB!
You'll thank me later!
No comments:
Post a Comment