none
DBCC SHRINKFILE: Page could not be moved because it is a work table page. RRS feed

  • Question

  • tempdb is auto grown to max size and when i try to shrink below is the error its throwing can anyone help on this 

    DBCC SHRINKFILE: Page could not be moved because it is a work table page.

    i try to clear all cache still no luck online they are suggesting restart sql but still i am looking for any other option any help is highly appreciated 

    Thanks

    Monday, December 16, 2013 7:48 PM

All replies

  • Find out why is it growing.  SQL Server restart is the simplest shrink.

    I am moving it to t-sql.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, December 16, 2013 7:57 PM
    Moderator
  • Try this please:

    DBCC FREEPROCCACHE
    GO
    USE [tempdb]
    GO
    DBCC SHRINKFILE (N'tempdev' , 5000)
    GO
    

    For more info please visit this page:

    How to Shrink TempDB in SQL 2005


    sqldevelop.wordpress.com

    Monday, December 16, 2013 8:06 PM
  • Hi,

    That's because there are active transactions manipulating the pages you're shrinking,

    probably trying to create, write or read some temporary tables on tempdb.

    Please, run DBCC OPENTRAN and check there are no open transactions.

    DBCC OPENTRAN('tempdb')

    Or, for all databases :

    exec sp_msforeachdb 'print ''?''; dbcc opentran (0)'


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu


    Monday, December 16, 2013 8:10 PM
  • there are no open transactions i would like to avoid restart sql its a critical machine  i tried clearing all caches and  i added another data file to tempdb to still shrink wont work on tempdev 
    Monday, December 16, 2013 8:13 PM
  • Hi,

    What SQL release are you running?

    Is it SQL 2005?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Monday, December 16, 2013 8:17 PM
  • sql server 2008 r2 
    Monday, December 16, 2013 8:18 PM
  • Do you have any databases that uses snapshots?

    select * from sys.databases
    where  (snapshot_isolation_state = 1 or is_read_committed_snapshot_on = 1)
    and database_id > 4

    It could be the version store that taking up the space.

    If this is a production system, I would look for more disk to grow into.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 16, 2013 10:54 PM