SQL Server Performance

SQL Server 2005 - tempdb and Temporary Tables

Discussion in 'Getting Started' started by lcerni, Jan 14, 2009.

  1. lcerni New Member

    I am working in SQL Server 2005 SP2.
    I have tried deleting these temporary tables from tempdb but cannot. I do not know if the front end application is creating them or if developers are and they are forgetting to delete them at the end. The only way to eliminate them is by rebooting the server. Is there another way? “DROP TABLE dbo.#1088713C” does not work.
    I get the error message
    “Msg 3701, Level 11, State 5, Line 1
    Cannot drop the table '#1088713C', because it does not exist or you do not have permission.”. I have tried to copy and paste a picture but I could not so I have typed the directory structure below to give you an idea of what I am talking about.System Databases
    Temporary Tables
  2. ndinakar Member

    Run a server side trace (or profiler) and filter by tempdb and see who is creating them. Kill them at the source.
  3. satya Moderator

    Do you really have a space problem on the disk where TEMPDB is located?
    If not simply advice to the developers and the users who has such permissions to create, to explain the process to drop the temp. objects when the process is finished. If no avail then simply takeout the permissions.
  4. lcerni New Member

    No, we don't have a space issue. We have tempdb on its own drive with 272GB. I am just trying to manage the size. I have an alert that goes off when the data file grows larger than 700MB. I tried in SQL Server 2005 to shrink the database and I could not. In SQL Server 2000 when this happens I have no issues shrinking the database.
  5. satya Moderator

  6. drmccue New Member

    I'm piggy-backing on this topic because I think what I'm observing is related to the original post.
    I'm a developer on a product that stores its data in SQL Server. Recently a customer asked us about temporary tables that he insisted we were creating and leaving behind in tempdb. The customer claims that tempdb is growing to 16 GB a night, and the only way to free up the space is to stop and start the SQL Server service. I assured one of our tech support personnel that our temp tables are created in stored procedures, so when the stored procedures go out of scope, the temp tables lose their context and SQL Server removes them.
    However, the tech support person reproduced the problem. So then I attempted to reproduce the problem. Sure enough, all it took was a login to our application to produce anywhere from 1 to 3 temp tables in tempdb, and they all had these 8-character hex code names, just like the original poster described in this post.
    I went through our stored procedure code and found no ##tables (global temp tables), so I knew these couldn't be that type of temp table. Eventually I used SQL Server Profiler to track all stored procedures called during our login process. After stepping through, I found the one that was creating these temp tables. This particular stored procedure was a wrapper to a user-defined function which returns a table (also referred to as a table-valued function).
    Further investigation on the Internet has revealed to me that in SQL Server 2005, tempdb is now "responsible for" table-valued functions. After querying INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS, I learned that these tables in tempdb, which our product did not explicitly create, are being implicitly created as a result of a call to a table-valued function. I also learned that the information returned by these table-valued functions is cached -- it appears to me that the caching is taking place in tempdb and the cache is never being freed until the server is stopped and restarted.
    What I have yet to understand is (1) why these temp tables are persisting (until the SQL Server service is restarted), when the table-valued function goes out of scope, and (2) how I can learn anything about the contents and/or size of these temp tables when I can't query them directly and exec sp_spaceused <temp_table_name> fails because the temp table in question does not exist in whatever context I use to run it. If anyone has any ideas, I have open ears.
    I borrowed a simple table-valued function I found on the Internet to move my observations from the context of our product to SQL Server in general, and I observed the same results. Here is that function (thanks to Andrew Novick, who published his PowerPoint presentation online). If you create this function, and then you call it by "SELECT Number, Factorial FROM udf_FactorialsTAB (10)", for example, I expect that you will see a seemingly persistent temp table with an 8-character hex code name appear in tempdb.
    CREATE FUNCTION [dbo].[udf_FactorialsTAB] (@N int )
    RETURNS @Factorials TABLE (Number INT, Factorial INT)
    SELECT @I = 1, @F = 1
    WHILE @I < = @N BEGIN
    SET @F = @I * @F
    INSERT INTO @Factorials VALUES (@I, @F)
    SET @I = @I + 1
    END -- WHILE

    Dan McCue
  7. satya Moderator

    WElcome to the forums.
    Thats a very interesting observation on the problem that you were able to get to the roots for some extent.
    As you may know TEMPDB is heavily used within SQL 2005 causing it to grow unexpectedly when you have such a series of table valued functions, as it needs a space to play around for the process execution. Having such a knowledge of the top tempdb-consuming processes and their times of occurrence is an essential goal of any general database-performance monitoring plan. Reducing tempdb utilization by targetting top processes can result in overall lower disk I/O, better optimized queries and a generally more responsive and more scalable database application.
    Coming to your questions:
    1) why these temp tables are persisting (until the SQL Server service is restarted), when the table-valued function goes out of scope?
    SQL Server 2005 caches temporary objects. When table-valued functions, table variables, or local temporary tables are used in a stored procedure, function, or trigger, the frequent drop and create of these temporary objects can be time consuming. This can cause contentions on tempdb system catalog tables and allocation pages. In SQL Server 2005, these are cached. That means that dropping and creating temporary objects is very fast. When SQL Server drops a temporary object, it does not remove the catalog entry for the object. If a temporary object is smaller than 8 MB, then one data page and one IAM page are also cached so that there is no need to allocate them when re-creating the objects. If a temporary object is larger than 8 MB, defer drop is used. When tempdb is low on space, SQL Server frees up the cached temporary objects. You can drop the associated stored procedure(s) or free the procedure cache to get rid of these temporary tables.
    (2) how I can learn anything about the contents and/or size of these temp tables when I can't query them directly and exec sp_spaceused <temp_table_name> fails because the temp table in question does not exist in whatever context I use to run it
    As they are TEMP tables it may exist or not, this is why your query fails. HEre I have blogged http://sqlserver-qa.net/blogs/perftune/archive/tags/tempdb/default.aspx few things to watch about TEMPDB.
  8. drmccue New Member

    Thanks for the response.
    Because of your suggestion about freeing the procedure cache, I learned about DBCC FREE PROCCACHE. Dropping the stored procedure is not a viable option in our application, but FREE PROCCACHE Is one of a number of options (each of which clears the procedure cache) that we have presented to the customer.
    Best regards,
    Dan McCue

Share This Page