none
TEMPDB - DBCC SHRINKFILE Does not free disk space (SQL SERVER 2008) RRS feed

  • Question

  • After a sudden growth of the TEMPDB data file to approximately 66 GB on production environment i have followed the second method available on MS KB (Method 2: Use the DBCC SHRINKDATABASE command) :

    https://support.microsoft.com/en-us/kb/307487

    However, after issuing the below script to shrink the file it didn't work and the file remains not shrink with the same 66 GB although the used space of the file now is  220 MB and free space is about 65 GB:

    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
     

    Is there any required actions to be applied prior to running the DBCC SHRINKFILE script ?

    Update  :

    When we try the SHRINKDATABASE option , one of the temp db files got shrink while the other showed the following message:

    DBCC SHRINKDATABASE: Page 1:8477818 could not be moved because it is a work table page.

    Regards


    • Edited by Osama Waly Sunday, November 29, 2015 4:56 PM Updates
    Saturday, November 28, 2015 11:24 AM

Answers

  • It is pretty common to have shrink for tempdb be blocked by concurrent work done in SQL Server. Be happy with what shrinkage you have achieved already, possibly create another file on some other disk so tempdb can expand there, or expedite re-start of your SQL Server so tempdb can be recreated (whichever best handles your current problem situation).

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, November 29, 2015 5:00 PM
    Moderator

All replies

  • Normally you need to restart the SQL Service to take effect this Shrink operation. If you are in a position cant perform the restart please apply as mentioned in below link

    http://sqlsunday.com/2013/08/11/shrinking-tempdb-without-restarting-sql-server/


    • Edited by ShabSQL Saturday, November 28, 2015 12:33 PM
    Saturday, November 28, 2015 11:35 AM
  • Usually the rule is, take a backup, shrink the db (or the logs), drop the backup. If you don't take the backup sometime the db maintain the size. Weird but true.

    Please mark as answer if this post helped you. 

    Saturday, November 28, 2015 11:07 PM
  • This is tempdb, so taking backup doesn't apply here.

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, November 29, 2015 8:57 AM
    Moderator
  • Normally you need to restart the SQL Service to take effect this Shrink operation.

    No, you don't.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Sunday, November 29, 2015 9:04 AM
    Moderator
  • In effect, by the way that we are talking of the Temp db, is not a bad idea to restart the entire instance. Probably there is a bad query running. 
    Sunday, November 29, 2015 9:38 AM
  • Normally you need to restart the SQL Service to take effect this Shrink operation.

    No, you don't.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi,

    here we are talking about TempDB , How to shrink the tempdb database in SQL Server


    • Edited by ShabSQL Sunday, November 29, 2015 10:01 AM
    Sunday, November 29, 2015 10:01 AM

  • When we try the SHRINKDATABASE option , one of the temp db files got shrink while the other showed the following message:

    DBCC SHRINKDATABASE: Page 1:8477818 could not be moved because it is a work table page.

    Sunday, November 29, 2015 4:57 PM

  • When we try the SHRINKDATABASE option , one of the temp db files got shrink while the other showed the following message:

    DBCC SHRINKDATABASE: Page 1:8477818 could not be moved because it is a work table page.

    Sunday, November 29, 2015 4:57 PM
  • It is pretty common to have shrink for tempdb be blocked by concurrent work done in SQL Server. Be happy with what shrinkage you have achieved already, possibly create another file on some other disk so tempdb can expand there, or expedite re-start of your SQL Server so tempdb can be recreated (whichever best handles your current problem situation).

    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, November 29, 2015 5:00 PM
    Moderator
  • Hi,

    In most cases, work tables are related to query plans. Try the following commands and shrink the tempdb

    DBCC FREESYSTEMCACHE('ALL')

    DBCC FREEPROCCACHE

    You may experience a little bit performance issue due to recreation of query plans.

    Thanks,

    Rajeev

    Sunday, November 29, 2015 5:36 PM
  • In most cases, work tables are related to query plans. Try the following commands and shrink the tempdb

    Only indirectly. The existence of a query plan does not mean that the work table is there. The work table only exists while the query is executing. So it is quite pointless to flush the plan cache. And in fact it is not going to help, becuase the query may still be executing.

    Sunday, November 29, 2015 7:55 PM