TempDB Full Problems\Solutions

January 25, 2000


The tempdb is used for creating temporary tables or storing temporary information. For example, a temporary table in a stored procedure or sql statement may create a temporary work table as a result of a query with a 'group by' or 'order by' clause. The two most common database errors are: 1) The database log ran out of space and 2) The database ran out of space. This problem deals with the tempdb running out of space.


The Tempdb on my SQL Server (V6.5, SP5A, NT 4.0 SP3) has a tempdb that was filling up about every ten days. (The tempdb log is okay, it is not filling up). Tempdb is defined as 400 MB. In order to clear out tempdb the server had to be stopped and restarted.


Reveiwed all the client machines' ODBC DSNs. (You can do this with Control Panel). There is a box that says "generate temporary stored procedures for prepared statements". Unchecked this box. SQL Server itself is pretty good about freeing up the objects it creates in tempdb, ho wever, ODBC is not, and often creates objects it doesn't really need to create. This resolved my specific problem.