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

    Question

  •  

    Hi,

     

    I issued this command on Tempdb but it doesnot shrink the file.

     

    dbcc shrinkfile (tempdev_3,1)

    go

     

    Messages:

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

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

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

     

    I have  checked that there are no tables associated with any user in tempdb. Any help is appreciated.

     

    Regards,

    Razi

    Friday, July 20, 2007 3:06 PM

Answers

  • I realize this is an old thred but I have found that in most cases work tables are related to Query Plans.

    Try issuing the following commands and the shrinking the tempdb:

    DBCC

     

    FREESYSTEMCACHE ('ALL')

    DBCC FREEPROCCACHE

    there will be some performance hit for this as SQL will have to recreate its Query Plans, but it should allow you to shrink you TEMPDB.

    Hope this helps,

    M Rose

    Wednesday, June 09, 2010 10:02 PM

All replies

  •  

    I bounced the SQL Server service (which I didnt wanted ) and re-claimed the space. Any insights???

    Friday, July 20, 2007 4:24 PM
  • This might have some bearing into my post on TempDB. Some process was likely performing work in the tempdb that would prevent the file from being resized. if it was a temporary object, you would have had to figure out how to query tempdb to see the temp object (which i can provide a simple query for) because you cannot normally see these objects. if a connection was left open by an application this would also apply, because the temp object would still be associated with that connection.
    Friday, July 20, 2007 6:16 PM
  • Hi,

     

    Please, could you post here the query to see temp objects.

     

    Thanks.

    masla

    Monday, July 23, 2007 9:36 AM
  • this basically is a re-write of an existing stored procedure that will show

    temp tables in the tempdb, and their relative size. I also have a query to

    show the size of tempdb in MB that i used with this one, it is also a modification

    to an existing system stored proc that shows allocated and free space in MB.

     

     

    --10 june 2007 slane

    --shows temp tables in the

    --tempdb

    use tempdb

    declare @id int

    declare @dt smalldatetime

    create table #spt_space_all

    (

    id int,

    name varchar(500),

    rows varchar(200) null,

    reserved varchar(200) null,

    data varchar(200) null,

    index_size varchar(200)null,

    unused varchar(200) null,

    create_date smalldatetime null,

    )

    declare TMP_ITEMS CURSOR LOCAL FAST_FORWARD for

    select id from sysobjects

    where xtype='U'

    open TMP_ITEMS

    fetch next from TMP_ITEMS into @id

    declare @pages int

    WHILE @@FETCH_STATUS = 0

    begin

    create table #spt_space

    (

    id int,

    rows int null,

    reserved dec(15) null,

    data dec(15) null,

    indexp dec(15) null,

    unused dec(15) null

    ,

    create_date smalldatetime null,

    )

    set nocount on

     

    if @id is not null

     

    set @dt = (select crdate from sysobjects where id=@id)

    begin

    /*

    ** Now calculate the summary data.

    ** reserved: sum(reserved) where indid in (0, 1, 255)

    */

    insert into #spt_space (reserved)

    select sum(reserved)

    from sysindexes

    where indid in (0, 1, 255)

    and id = @id

    /*

    ** data: sum(dpages) where indid < 2

    ** + sum(used) where indid = 255 (text)

    */

    select @pages = sum(dpages)

    from sysindexes

    where indid < 2

    and id = @id

    select @pages = @pages + isnull(sum(used), 0)

    from sysindexes

    where indid = 255

    and id = @id

    update #spt_space

    set data = @pages

     

    /* index: sum(used) where indid in (0, 1, 255) - data */

    update #spt_space

    set indexp = (select sum(used)

    from sysindexes

    where indid in (0, 1, 255)

    and id = @id)

    - data

    /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

    update #spt_space

    set unused = reserved

    - (select sum(used)

    from sysindexes

    where indid in (0, 1, 255)

    and id = @id)

    update #spt_space

    set rows = i.rows

    from sysindexes i

    where i.indid < 2

    and i.id = @id

    update #spt_space set create_date=@dt

    end

    insert into #spt_space_all

    select name = @id,object_name(@id),

    rows = convert(char(11), rows),

    reserved = ltrim(str(reserved * d.low / 1024.,15,0) +

    ' ' + 'KB'),

    data = ltrim(str(data * d.low / 1024.,15,0) +

    ' ' + 'KB'),

    index_size = ltrim(str(indexp * d.low / 1024.,15,0) +

    ' ' + 'KB'),

    unused = ltrim(str(unused * d.low / 1024.,15,0) +

    ' ' + 'KB'),create_date

    from #spt_space, master.dbo.spt_values d

    where d.number = 1

    and d.type = 'E'

    drop table #spt_space

    FETCH NEXT FROM TMP_ITEMS

    INTO @id

    end

    CLOSE TMP_ITEMS

    DEALLOCATE TMP_ITEMS

    select * from #spt_space_all where [name] not like '%#spt_space_all%'

    drop table #spt_space_all

    GO

    Monday, July 23, 2007 6:55 PM
  • I have the same issue.

    I do not have any user connections to the server except for me.

    I still cannot shrink down the tempdb unless I restart the SQL Server Service.

    There are temp tables that exist in the tempdb databases but I could not drop them.

     

    I get an error saying:

    Msg 3701, Level 11, State 5, Line 1

    Cannot drop the table '#344946C3', because it does not exist or you do not have permission.

     

    It is great that I could see the temp tables in tempdb but what can be done to shrink down

    tempdb without restarting SQL Server Service.

     

    Any suggestions would be greatly appreciated.

     

    Thanks.

    Monday, November 17, 2008 2:24 PM
  • If you have persistent tables left in tempdb, then you probably aren't doing good cleanup in code, and/or you have not patched your SQL Server beyond SP2.  There was a fix post SP2 for the following problem:

     

    50000874 When the stored procedure runs outside an explicit transaction, a temporary table in a stored procedure is not cleaned up correctly.

     

    You can get the post SP2 update from the following link:

     

    Cumulative hotfix package (build 3152) for SQL Server 2005 Service ...

     

     

    Monday, November 17, 2008 5:42 PM
    Moderator
  • Thanks Jonathan. 

    We currently are at Build 3175

    I would tend to agree that the code is not doing good cleanup work if any.

    Vendor code tend to be that way.

    With that said, is there no other recourse but to restart the SQL Server Service?

    I can't drop the temp tables.

     

     

    Monday, November 17, 2008 8:46 PM
  • I realize this is an old thred but I have found that in most cases work tables are related to Query Plans.

    Try issuing the following commands and the shrinking the tempdb:

    DBCC

     

    FREESYSTEMCACHE ('ALL')

    DBCC FREEPROCCACHE

    there will be some performance hit for this as SQL will have to recreate its Query Plans, but it should allow you to shrink you TEMPDB.

    Hope this helps,

    M Rose

    Wednesday, June 09, 2010 10:02 PM
  • I can confirm that this does help with the ability to shrink the tempdev file in tempdb when you get this error.

    Many thanks, Mike Rose

    Thursday, October 28, 2010 5:24 PM
  • Unfortunately for me, this did not work. Here is my bizarre situ:

    select * from sys.dm_tran_active_transactions s1 inner join sys.dm_tran_database_transactions s2
    on s1.transaction_id = s2.transaction_id
    where transaction_begin_time < '11/06/2010'

    This returns 19 rows all on tempdb, all named 'worktable', all read-only transactions, state=3 ("3 = The transaction has been initialized but has not generated any log records."), NULL or zero for all other columns. There are no connections listed in Activity Monitor with login dates prior to the trans begin times of these rows. These are just horked up table variables!

    I would hope to kill the sessions and have the objects cleaned up. Then I could shrink tempdb. However,

            select * from sys.dm_tran_session_transactions

    returns no rows. So these "transactions" aren't bound to any sessions, right? So now what do I do? Well, I went to figure out which sessions are owning these worktable objects:

    select session_id, database_id,
        user_objects_alloc_page_count,
        user_objects_dealloc_page_count,
        internal_objects_alloc_page_count,
        internal_objects_dealloc_page_count,
        waittime,lastwaittype,
        login_time, last_batch, status, cmd, loginname, sql_handle
    from sys.dm_db_task_space_usage s1 inner join sys.sysprocesses s2
    on s1.session_id = s2.spid

    And Lo and Behold! There is no sql_handle for these (sql_handle and context_info all zeroes)! These are all objects owned by core SQL Server pieces that are either in status of background or sleeping:

    waittime       lastwaittype                      cmd
    0             SOS_SCHEDULER_YIELD             RESOURCE MONITOR
    624           LAZYWRITER_SLEEP                LAZY WRITER    
    1295962       LOGMGR_QUEUE                    LOG WRITER     
    2043          REQUEST_FOR_DEADLOCK_SEARCH     LOCK MONITOR   
    1346638725    KSOURCE_WAKEUP                  SIGNAL HANDLER 
    0             MISCELLANEOUS                   TASK MANAGER   
    2932          SQLTRACE_BUFFER_FLUSH           TRACE QUEUE TASK
    0             MISCELLANEOUS                   UNKNOWN TOKEN  
    1346641596    BROKER_TRANSMITTER              BRKR TASK      
    1346643905    ONDEMAND_TASK_QUEUE             TASK MANAGER   
    1231097       CHECKPOINT_QUEUE                CHECKPOINT     
    0             MISCELLANEOUS                   TASK MANAGER   
    1299160       BROKER_EVENTHANDLER             BRKR EVENT HNDLR
    1346641596    BROKER_TRANSMITTER              BRKR TASK      
    0             MISCELLANEOUS                   TASK MANAGER   
    0             MISCELLANEOUS                   TASK MANAGER   
    0             MISCELLANEOUS                   TASK MANAGER   
    0             MISCELLANEOUS                   TASK MANAGER   
    0             MISCELLANEOUS                   TASK MANAGER   
    0             MISCELLANEOUS                   TASK MANAGER   
    0             MISCELLANEOUS                   TASK MANAGER   
    0             MISCELLANEOUS                   TASK MANAGER   

    So, bottom line...I'm screwed. All I can do is restart SQL Server. I see no way of killing these worktable objects manually.

     

    Stuff tried/looked at:

    DBCC FREESYSTEMCACHE ('ALL')
    DBCC FREEPROCCACHE
    DBCC SHRINKFILE('tempdev', 200, NOTRUNCATE )
    DBCC SHRINKFILE('tempdev', 200 )

    DBCC SHRINKFILE: Page 1:2933472 could not be moved because it is a work table page.
    DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
    ------ ----------- ----------- ----------- ----------- --------------
    2      1           2933480     1024        304         304

    There is tremendous free space:

    sp_spaceused @updateusage=true

    database_name     database_size      unallocated space
    ---------------------- ------------------ ------------------
    tempdb                 22918.56 MB        22916.38 MB

    but it seems there is a page begin held on to that can't be moved. There is nothing runnable in Activity Monitor. I configured a distributor database for replication but created no publishers or publications yet.

     

     

     

     

    Sunday, November 07, 2010 7:33 AM
  • One other note. I find it completely ironic that other posts on this issue all pan bad coders for not cleaning up appropriately. Here is looks like SQL Server itself is not cleaning up it's temp objects! If I'm reading this right and this is true, then we need a fix Microsoft! I can't just be restarting sql server after some major bulk-load or index rebuild creates a bunch of tempdb allocations that never get cleaned up and I have to restart the sql server. Not good.
    Sunday, November 07, 2010 7:36 AM
  • Well perhaps you need to 'optimize' your load to work with tempdb

    http://technet.microsoft.com/en-us/library/cc966545.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, November 07, 2010 8:12 AM
    Answerer
  • Hi all. I am fully agree with Quickdraw and do not understand what is meaning of  Uri Dimant post.

    The issue is that SQL Server was unable to cleanup one of TempDB due to "Page could not be moved because it is a work table page". I did everything I have to do for understanding the problem and even (after I realized that nothing helps) run DBCC FREEPROCCACHE and even restarted the instance (and the machine too). Hundreds of articles and blogs but nothing... Pretty nothing... Is there any way to resolve the issue?

    Thanks

    P.S. Using SQL Server 2012 SP1


    Monday, January 27, 2014 10:25 AM
  • USE [tempdb]

    GO

    DBCC DROPCLEANBUFFERS

    GO

    DBCC FREEPROCCACHE

    GO

    DBCC FREESESSIONCACHE

    GO

    DBCC FREESYSTEMCACHE ( 'ALL')

    GO

    DBCC SHRINKFILE (N'tempdev2' , EMPTYFILE)

    GO

    ALTER DATABASE [tempdb]  REMOVE FILE [tempdev2]

    GO

    Thursday, September 25, 2014 1:20 AM
  • The script just works for me thank you !!!

    USE [tempdb]

    GO
    DBCC DROPCLEANBUFFERS
    GO
    DBCC FREEPROCCACHE
    GO
    DBCC FREESESSIONCACHE
    GO
    DBCC FREESYSTEMCACHE ( 'ALL')
    GO
    DBCC SHRINKFILE (N'tempdev2' , EMPTYFILE)
    GO
    ALTER DATABASE [tempdb]  REMOVE FILE [tempdev2]
    GO

    Wednesday, March 08, 2017 11:28 AM
  • This worked well for me
    Wednesday, August 23, 2017 9:40 PM