Post reply

TempDB won't shrink, Phantom data in sys.dm_dm_file_space_usage?

  • December 1, 2011 at 7:06 pm

    #247183

    I have read a lot of commentary on the internet regarding the TempDb's database not shrinking. However, I haven't come across anything that addresses what I think may be going on.

    Our TempDB data file takes up all the space on drive but almost all of that space is unallocated and should be shrinkable via DBCC ShrinkFile or the other methods one can use to shrink the TempDB. (I don't want to restart the server though this does solve the problem.)

    When I run the following DMVs, what I notice is that the Session and Task DMVs show zero pages for all sessions and tasks. (except for a small number of pages for my session.)

    SELECT * FROM sys.dm_db_file_space_usage

    SELECT * FROM sys.dm_db_session_space_usage

    SELECT * FROM sys.dm_db_task_space_usage

    However, and here is the mystery, the File DMV shows small amounts of allocated pages for version store, user objects, internal objects, and mixed extents. Because the other DMVs show nothing, I suspect the File DMV results are either inaccurate or represent orphaned data. Furthermore, I believe it is this data which is preventing the TempDB from shrinking.

    Does this theory hold any water? Does anyone have any idea how I can either release the pages shown in the File DMV and\or determine which process those pages are related to?

    Btw, I have read GilaMonster's and other's comments about not shrinking the TempDB. I agree. In our case the growth is caused by poor design which will take a while to fix (bureaucratic inertia, scarce resources, etc.). In the meantime I would like to reduce the TempDb so its growth doesn't crash the app.

    Thank you very much!

    Rod

  • December 2, 2011 at 3:04 am

    #1415771

    Try issuing DBCC FREESYSTEMCACHE('All') and then you should be able to shrink tempdb.

    See here for more details: http://www.sqlservercentral.com/scripts/tempdb/72007/

    -- Gianluca Sartori

  • December 2, 2011 at 4:33 am

    #1415796

    Hi,

    Ur doing this for sql server 2005 or 2008

  • December 2, 2011 at 5:50 am

    #1415831

    bmw110001 (12/2/2011)

    Hi,

    Ur doing this for sql server 2005 or 2008

    I suppose 2005, since this is the 2005 forum.

    -- Gianluca Sartori

  • December 2, 2011 at 4:36 pm

    #1416217

    Take a look at DBCC UPDATEUSAGE in case it can assist. I don't recall right off if the DMV's you are using are ones that need to be kicked a little to be correct.

    Your theory could very well be correct.

    http://msdn.microsoft.com/en-us/library/aa258283(v=sql.80).aspx

  • December 3, 2011 at 2:51 am

    #1416259

    use tempdb

    go

    dbcc shrinkfile (tempdev, 'target size in MB')

    go

    -- this command shrinks the primary data file

    dbcc shrinkfile (templog, 'target size in MB')

    go

    -- this command shrinks the log file, look at the last paragraph.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply