none
Temp DB occupies maximum space

    Question

  • I am having issues with temp db space, which grows very large every week, I heard shrinking temp db is not a good idea to do, which I am doing it now temporarily to shrink the space, can someone help me how to troubleshoot not to increase the temp db space, I am using #temp tables, cursors, unions and indexes on some of the temp tables in sp's, does it make any difference if i drop the temp tables and indexes after the proc ran successfully?.
    Monday, August 30, 2010 3:07 PM

Answers

  • The first thing you need to do is set a cap on tempdb growth, so you can preallocate storage.  Tempdb uses a proporitional fill algorithum, so pre alloacting space to it help alliviate the growth cost penalty and it ensures tempdb does not use more space than alloted. You may need to add more storage to your tempdb LUNs/Disk, as you may be using tempdb too much for your environment.  Are you doing anything like index rebuilds or perhaps dropping REALLY, REALLY large temp tables?  If so, tempdb may be doing a defered drop, which may not drop large objects immediately.

    Here is an excerpt from a white paper.

    link: http://technet.microsoft.com/en-us/library/cc966545.aspx

    There is now deferred drop in tempdb. This means, for example, that when a large temporary table is dropped by an application, it is handled by a background task and the application does not have to wait. This results in faster response time to applications.

    Worktable caching is improved. When a query execution plan is cached, the work tables needed by the plan are not dropped across multiple executions of the plan but merely truncated. In addition, the first nine pages for the work table are kept. In SQL Server 2000, the work tables used during query plan execution are dropped. Because the work table is cached, the next execution of the query is faster. When the system is low on memory, the execution plan may be removed from the cache and the associated work tables dropped as well. Both SQL Server 2000 and SQL Server 2005 use a small global pool of pre-allocated pages and extents that make the initial creation of work tables faster.

    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.

    • Edited by Adam HainesModerator Monday, August 30, 2010 3:37 PM formatting
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:18 AM
    Monday, August 30, 2010 3:32 PM
  • Two things you could be facing:

    • Objects are not cleaned up from tempdb after usage (Try to see how much space is actually used using the default reports from SSMS)
    • Your queries consume more and more space for sorting / grouping over the time

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:18 AM
    Monday, August 30, 2010 3:44 PM

All replies

  • The first thing you need to do is set a cap on tempdb growth, so you can preallocate storage.  Tempdb uses a proporitional fill algorithum, so pre alloacting space to it help alliviate the growth cost penalty and it ensures tempdb does not use more space than alloted. You may need to add more storage to your tempdb LUNs/Disk, as you may be using tempdb too much for your environment.  Are you doing anything like index rebuilds or perhaps dropping REALLY, REALLY large temp tables?  If so, tempdb may be doing a defered drop, which may not drop large objects immediately.

    Here is an excerpt from a white paper.

    link: http://technet.microsoft.com/en-us/library/cc966545.aspx

    There is now deferred drop in tempdb. This means, for example, that when a large temporary table is dropped by an application, it is handled by a background task and the application does not have to wait. This results in faster response time to applications.

    Worktable caching is improved. When a query execution plan is cached, the work tables needed by the plan are not dropped across multiple executions of the plan but merely truncated. In addition, the first nine pages for the work table are kept. In SQL Server 2000, the work tables used during query plan execution are dropped. Because the work table is cached, the next execution of the query is faster. When the system is low on memory, the execution plan may be removed from the cache and the associated work tables dropped as well. Both SQL Server 2000 and SQL Server 2005 use a small global pool of pre-allocated pages and extents that make the initial creation of work tables faster.

    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.

    • Edited by Adam HainesModerator Monday, August 30, 2010 3:37 PM formatting
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:18 AM
    Monday, August 30, 2010 3:32 PM
  • Two things you could be facing:

    • Objects are not cleaned up from tempdb after usage (Try to see how much space is actually used using the default reports from SSMS)
    • Your queries consume more and more space for sorting / grouping over the time

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    • Marked as answer by KJian_ Tuesday, September 07, 2010 3:18 AM
    Monday, August 30, 2010 3:44 PM
  • Any progress?
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Monday, September 06, 2010 5:40 AM