TempDB - TempDev will not shrink - Help!

  • dbamohsin

    SSCommitted

    Points: 1948

    Hi Everyone,

    I initially did a search on the forum but nothing really answers my problem.

    I have a Tempdb which has grown to around 16gb and my attempts at shrinking it do not work at all! I realise that the problem is resolved with a restart of sql server but this is unacceptable in my production environment (and a restart should never be a norm to fix a problem!)

    Use [Tempdb]

    GO

    SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', * FROM sys.database_files;

    tempdev

    O:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf

    Total Size in MB: 15891

    Available Size in MB: 15783.187500

    templog

    O:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf

    Total Size in MB: 259

    Available Size in MB: 247.187500

    select * from sys.dm_db_file_space_usage

    Unallocated_extent_page_count: 2020560

    The unallocated extent page count when divided by 128 (number of pages in a mb) = 15785.625 mb

    Ive tried the following:

    USE [tempdb]

    GO

    DBCC SHRINKFILE (N'tempdev' , 1024)

    GO

    USE [tempdb]

    GO

    DBCC SHRINKDATABASE (N'tempdb')

    GO

    USE [tempdb]

    GO

    DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)

    GO

    the shrink file gui window of tempdev shows allocated space of 8.00MB and available free space of -96.19mb ????

    Can anyone help??

    My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Why do you need to shrink it? If it's grown that large it means that something within your production workload needs a 16 GB tempDB. If you do somehow force a shrink, it could well grow back to that size. Unless you are running out of disk space, there is no good reason to shrink the database. Empty space within won't cause issues.

    If your workload is such that tempDB needs to be that size, then make it that size from startup.

    Oh, and you may want to note that running Shrink on TempDB while it's in use can result in a corrupt tempDB. There is a kb article on that - http://support.microsoft.com/kb/307487

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sebastien piche

    SSC Eights!

    Points: 910

    is not good idea shrink tempDB depend activity on your database but 16gb is not unusual my production server have 40Gb for tmpdb into 6 physical files. maybe considere add additional disk to have suffisent space.

  • dbamohsin

    SSCommitted

    Points: 1948

    I know that tempdb can get currupted and i have read the various Ms kb articles. I wouldnt be doing this during the day, but the transaction that caused the tempdb to grow so much was a one off and not our standard tempdb growth.

    i would be shrinking tempdev to around 10gb which is enough to handle our everyday transactions.

    I dont have the capacity to maintain a 16gb tempdb which is why i want to shrink it.

    The point is not why i am trying to do this but why it is not working when i try the options given by Microsoft

    My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Did you run the 'update usage' that the kb article refered to?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Cowboy DBA

    SSCrazy

    Points: 2135

    We've had a similar issue on one of our environments. The only explanation I can give at the moment is similar to problems when attempting to shrink the transaction log file: if there is an active transaction at the end of the file then DBCC SHRINKFILE will not be able to shrink it. Like I allude to, this is only a guess.

    The script below should be able to tell you what pending I/O requests are on what particular database and file. That'll give you some idea regarding any (long/continuous) oustanding requests. Then maybe look at killing the spid which is causing the request which might enable you to shrink tempdb

    select

    database_id,

    file_id,

    io_stall,

    io_pending_ms_ticks,

    scheduler_address

    from sys.dm_io_virtual_file_stats(NULL, NULL)t1,

    sys.dm_io_pending_io_requests as t2

    where t1.file_handle = t2.io_handle

    The above advice might be a load of rubbish but definitely comes without warranty and I take no responsibility for bringing your company down to a grinding halt.:ermm:

  • dbamohsin

    SSCommitted

    Points: 1948

    Hi,

    I ran updateusage against tempdb and it didnt resolve the minus free space issue.

    Im a bit baffled about it all - a friend mentioned that it may be because the tempdev is holding data at the back of the file preventing a shrink (similar to the way a log would) but a reorganise would have resolved this.

    Is there any way to check this out?

    My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

  • Boolean_z

    SSCommitted

    Points: 1667

    GUI showing percent free in minus is common for large tempdb's, I dont know exactly why is this, but I have seem in several servers.

  • SUBRAHMANYA HEDGE

    Default port

    Points: 1404

    Can you try this? It worked for me...

    USE [tempdb]

    GO

    Checkpoint

    DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)

    GO

  • Cowboy DBA

    SSCrazy

    Points: 2135

    Or

    use tempdb

    backup log tempdb WITH NO_LOG

    dbcc shrinkfile (templog,0)

    dbcc shrinkfile (tempdev,0)

  • dbamohsin

    SSCommitted

    Points: 1948

    USE [tempdb]

    GO

    Checkpoint

    DBCC SHRINKFILE (N'tempdev' , 0, TRUNCATEONLY)

    GO

    This doesnt work for me - ive tried the checkpointing option before.

    Thanks anyway

    My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

  • dbamohsin

    SSCommitted

    Points: 1948

    use tempdb

    backup log tempdb WITH NO_LOG

    dbcc shrinkfile (templog,0)

    dbcc shrinkfile (tempdev,0)

    The log shrinks fine but the data file doesnt change in size

    My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

  • sebastien piche

    SSC Eights!

    Points: 910

    temdb data files move only after SQL service restart

    http://support.microsoft.com/kb/187824/en-us

  • David Levy

    SSCommitted

    Points: 1622

    SQL 2005 caches a lot of things in tempdb, most tied to a query plan. Try a DBCC FREEPROCCACHE then you should be able to shrink. I should mention though that you really do not want to shrink TempDB unless you are doing some specific maintenance like splitting it into multiple files.

  • *vivian*

    Say Hey Kid

    Points: 683

    David Levy

    SQL 2005 caches a lot of things in tempdb, most tied to a query plan. Try a DBCC FREEPROCCACHE then you should be able to shrink. I should mention though that you really do not want to shrink TempDB unless you are doing some specific maintenance like splitting it into multiple files.

    ---------------------------------------------------------------------------------------------

    ---------------------------------------------------------------------------------------------

    ---------------------------------------------------------------------------------------------

    wow, you are so great, I tried many ways to shrink tempdb online, finally after I run dbcc freeproccache as you suggested, and then I shrink it finally.

    Thanks!!!

Viewing 15 posts - 1 through 15 (of 27 total)

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