SQL Server Performance Forum – Threads Archive

TempDB is full

What a DBA should do if tempdb gets too big? Is recycling SQL Server the only solution? CanadaDBA

---

http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html fyi and most of it still applicable to SQL 2005. Few counters to watch:
Within 2005 certain changes to records in tempdb are no longer logged, as a way to reduce the amount of I/O traffic for tempdb, its log and the devices both are hosted on. INSERT actions only log what something has been changed to, and DELETE actions only log what was deleted. Only UPDATE actions log both, the original and changed data. Dynamic Management Views report back statistics about tempdb’s space usage, which can be retrieved with a query. For instance, SELECT SUM (unallocated_extent_page_count)*8 as [Free Space] FROM sys.dm_db_file_space_usage would return the total amount of free space in kilobytes for tempdb’s files. Two new performance counters, Temp Tables Creation Rate and Temp Tables for Destruction, are now available for SQL Server 2005.
Evaluate whether the query could be written in a more efficient fashion using different TSQL statements or expressions. Just blogged the samehttp://sqlserver-qa.net/blogs/tools…tempdb-is-full-in-sql-2000-2005-versions.aspx here.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Try the below T-SQL statement, it will show you which database has an open transaction, see how long it is open for. Usually if the transaction is open from more than hrs, that could be the culprit
sp_MSForEachDB ‘Print”?” Print”~~~~~~~~~~~~~~~” DBCC OPENTRAN (?)’
Sizing of Tempdb can be the other solution in such casea. Assuming that your applications requirement is such that you require large Tempdb just go ahead and give enough space for TempDB to work smoothly. However if you feel that the Tempdb requirement is too much, look into the link what Satya is talking about.
Girish Patil
MS-SQL DBA
http://www.facebook.com/p/Girish_Patil/517990969

---

Check the following too… Why is tempdb full, and how can I prevent this from happening
http://sqlserver2000.databases.aspf…nd-how-can-i-prevent-this-from-happening.html How to shrink the tempdb database in SQL
http://support.microsoft.com/kb/307487
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

The temp db is not full. so what else might be the problem

---

Preethi,
can you explain more about your question? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

sorry i posted in the wrong window.

---

If the TEMPDB sizing Is not a problem then you have to see the offending queries by performing few check with SP_WHO2 and PROFILER if needed.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

]]>