Tackling TempDB! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Tackling TempDB!

Hi All, I guess one component of SQL Server which keeps every DBA on tenterhooks is tempDB when it comes it to trouble shooting it. Well, i have a scenario which i guess everyone had faced some time or the other. If everyone can put their way of handling the situation, it would be very useful for others i guess: Scenario:
1. TempDb is situated on its own Drive with Autogrow set
2. You Receive an alert stating that tempdb Log is Full and take an backup. This alert gets generated for the ALL applications running on the SQL Sever at that moment.
3. You check and see that, the tempdb has grown to the maximum extent of the drive.
4. You tend to do any operation on the server and it throws a tempdb error.
5. You try to temporarily add a new Log file for tempdb on other drive but doesnt work as the tempdb log is full.
6. You check the active transaction in tempdb and kill it.
7. The Killed transaction goes into RollBack state.
8. It tries to RollBack but it needs tempdb for this and its full.
9. Its basically a Deadlock. Well, one answer i know here, Restart the SQL Server…….Any Other Opinions? Regards,
The causes for this issues are: -large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb; -any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb; -use of an ODBC DSN with the option ‘create temporary stored procedures’ set can leave objects there for the life of the connection. Usually, tempdb fills up when you are low on disk space, or when you have set an unreasonably low maximum size for database growth. Restarting SQL is a short term fix that will resize the TEMPDB to its original state and as an interim solution you can try
and check whether any open transactions in tempdb by using DBCC OPENTRAN. To avoid future issues in this regard:
-Try to make sure you have covering indexes for all large table that are used in queries that can’t use a clustered index / index seek. -Batch larger heavily-logged operations (especially deletes) that *might* overflow into tempdb into reasonable ‘chunks’ of rows, especially when joins are involved. Refer KBAhttp://support.microsoft.com/default.aspx/kb/834846 – webcast about tempdb. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.