SQL Server Performance

why i can't shrink tempdb data file

Discussion in 'SQL Server 2005 General DBA Questions' started by avipenina, Apr 28, 2009.

  1. avipenina New Member

    when i run the DBCC SHRINKFILE i get this error message

    DBCC SHRINKFILE: Page 1:3740312 could not be moved because it is a work table page.
    but the free space on the tempdb is 29GB.
    how can i know what is keeping my tempdb busy,so i can't shrink it?
    sp_spaceused =

    database_name database_size unallocated space
    -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
    tempdb 29971.25 MB 29219.73 MB

    reserved data index_size unused
    ------------------ ------------------ ------------------ ------------------
    1560 KB 648 KB 712 KB 200 KBTHX
    avipenina, Apr 28, 2009
    #2

  2. Luis Martin Moderator

    You can't shrink tempb in working hours.
    Tempdb reduce his space to minimum each time you down sql service.
    Also if tempdb need to grow you have to have plenty space for that. May be you have other drive and then I suggest to create other filegroup in tempdb in that drive.
    Luis Martin, Apr 28, 2009
    #3

  3. satya Moderator

    satya, Apr 29, 2009
    #4

  4. avipenina New Member

    I've to shrink it because someone make an incorrect query and it fill up the tempdb.
    know no one is working on the tempdb,but i still can't shrink it to lets say 10GB(now it's 30GB).
    what can i do to shrink it beside of restart the sql server service?
    THX
    avipenina, Apr 29, 2009
    #5

  5. Luis Martin Moderator

    Be sure you are the only user in all server and try to shrink.
    Luis Martin, Apr 30, 2009
    #6

  6. satya Moderator

    If there are any other open processes or existing executio of queries then you may not be able to shrink it efficiently, in this case look for a maintenance window to accomplish the task.
    satya, May 1, 2009
    #7

Share This Page