snapshot of window and spaceI am developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly .

Is there any solution to clean the tempdb file in microsoft sql server 2008 on a timely basis other than restarting the server ?

Thank you so much in Advance

  • You could analyse it and see what's being added to it, and truncating it regularly? Commented Jun 13, 2012 at 14:44
  • Are you using temp tables in your queries? Are you batching the queries properly? Commented Jun 13, 2012 at 16:54

6 Answers 6


Tempdb is growing like this because something is using it. No attempt to fix, clean or manipulate tempdb is going to work as long as this is going on.

To address this problem, you need to find out what is causing it to grow like this and then fix that problem first. This alone may resolve the whole issue and remove your need to do anything else with tempdb. In my experience, the most likely cause for something like this is an out-of-control Cross Join query (though there are lots of other possibilities).

  • 2
    In addition it may just not be sized appropriately to the work loaded being demanded.
    – Kuberchaun
    Commented Jul 14, 2012 at 16:02

We recently faced the same issue. I got a marvellous solution over here : Cleaning tempdb
This will definitely help you. All the best !!

  • Hi Thanks for your reply but this did not work . The query runs successfully but did not see any changes . I even tried using 1 hh instead of 12
    – Rishi Ranka
    Commented Jun 13, 2012 at 15:02
  • Probable reason for this can be that either you do not have the permission to drop tempdb objects or the objects are currently in use by some session (as you have reduced old objects' age from 12 to 1 hr).
    – Saurabh
    Commented Jun 13, 2012 at 15:11

Sharing a query to get the usage sizing. Little easier than MS kb query.

use tempdb

SELECT sum(unallocated_extent_page_count) [Free_Pages],
 (sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]
FROM sys.dm_db_file_space_usage;

developing a log analyzer tool and After queering it and generating reports Tempdb is getting full very quickly .

Depending on what your tool does when generating reports - creates lot of tempdb tables, spills data to tempdb when using sort operators, etc, I would suggest you to presize your tempdb.

You are better off optimizing the SQL that is generating the report. May be putting proper index/s will definitely help.

Its always a best practice to only query the data that you require.

Start by analyzing wait stats.

You can use sp_whoisactive to analyze whats going on your server instance.

Below query will help you to identify the sessions that use the tempdb heavily :

Lists the TempDB usage per each active session.
It helps identifying the sessions that use the tempdb heavily with internal objects.

When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of an inefficient plan or a missing index.

Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space. 

Ref: https://dba.stackexchange.com/a/19871/8783

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       -- Extract statement from sql text
                   ERQ.statement_start_offset / 2, 
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
FROM task_space_usage AS TSU
--- Changed from inner join to left outer join to return rows for sessions that aren't currently actively running queries.
left outer join sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP

Is there any solution to clean the tempdb file in microsoft sql server 2008 on a timely basis other than restarting the server ?

Use it carefully on a production server !!

The only possible alternative to restarting the service, is running

DBCC FREESYSTEMCACHE('ALL')-- that will clear all cached objects, including not only internal objects, but also cached query plans.

DBCC FREESYSTEMCACHE ('tempdb') -- clears cache for tempdb

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables

Also, refer to Difference between FreeProcCache and FreeSystemCache


If you have a nightly maintenance window for the DB the simplest method is to change your database from FULL logging to SIMPLE and then back to FULL again.

All other methods, e.g. http://support.microsoft.com/kb/307487 are overly complicated but you may be forced to consider them nonetheless.

  • 6
    tempdb is always simple. And this is generally a bad idea because of backup chain issues getting broken...
    – gbn
    Commented Jun 14, 2012 at 14:35
  • Yes, you will need to do backups after changing the logging.
    – Jimbo
    Commented Jun 15, 2012 at 12:08
  • We have full backup every week, diffs every morning and log backups every 15 minutes. After flipping the logging from full to simple and then back again we do the diff. All restores work correctly and we never have issues with large temp DBs...
    – Jimbo
    Commented Jun 15, 2012 at 12:20

you are seeing the default size of temp DB but not the consumed size . you may restart the SQL server services to clean the Temp DB.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.