why i can't shrink tempdb data file

  • Mad-Dog

    SSCertifiable

    Points: 6099

    Hi,

    my tempdb database is 30GB(datafile).

    i try to shrink it with all those options but no luck.

    use tempdb

    go

    DBCC SHRINKFILE (tempdev,5000)

    go

    DBCC SHRINKFILE (tempdev,truncateonly)

    go

    DBCC SHRINKDATABASE (tempdb,5000)

    --answer

    DBCC SHRINKDATABASE: File ID 1 of database ID 2 was skipped because the file does not have enough free space to reclaim.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    sp_spaceused

    --answer

    tempdb 29971.25 MB 29219.77 MB

    DBCC OPENTRAN

    --answer

    No active open transactions.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    i run this as well with no luck

    DBCC FREEPROCCACHE

    GO

    DBCC DROPCLEANBUFFERS

    go

    DBCC FREESYSTEMCACHE ('ALL')

    GO

    DBCC FREESESSIONCACHE

    GO

    dbcc shrinkfile (tempdev,5000)

    go

    how i can shrink the file without restart the sql server services?

    THX

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    Check http://support.microsoft.com/kb/307487 ... this is why I love Microsoft's KB

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Mad-Dog

    SSCertifiable

    Points: 6099

    this article not help me because i already did what is writing there and it's not working.

    what else i can do to reduce the data file?(with no sql restart)

    THX

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    Is your tempdb one data file? Just tempdb.mdf?

    What was the result of the shrinkfile on this file?

  • Mad-Dog

    SSCertifiable

    Points: 6099

    this article not help me because i already did what is writing there and it's not working.

    what else i can do to reduce the data file?(with no sql restart)

    THX

  • Mad-Dog

    SSCertifiable

    Points: 6099

    Steve Jones - Editor (4/27/2009)


    Is your tempdb one data file? Just tempdb.mdf?

    What was the result of the shrinkfile on this file?

    yse only one file.

    the results are the nothing happen,it's not shrink the file,the file stay as is.

  • Adam Bean

    One Orange Chip

    Points: 26440

    Mad dog, how big is the file? How much activity against tempdb? Sometimes a file won't shrink due to open transactions and will do so once it is idle. You may see some indication of this in the error log.

    ---
    SQLSlayer
    Making SQL do what we want it to do.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714080

    Are you sure there's free space in there? Perhaps there are temp objects preventing it from shrinking?

  • Lynn Pettis

    SSC Guru

    Points: 442091

    Try running this query and see what is out there in tempdb:

    select * from tempdb.sys.all_objects

    where is_ms_shipped = 0;

  • Adam Bean

    One Orange Chip

    Points: 26440

    Steve Jones - Editor (4/27/2009)


    Are you sure there's free space in there? Perhaps there are temp objects preventing it from shrinking?

    Good call, run this to verify:

    USE [tempdb]

    SELECT

    [name]

    ,CONVERT(NUMERIC(10,2),ROUND(/128.,2)) AS [Size]

    ,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2)) AS [Used]

    ,CONVERT(NUMERIC(10,2),ROUND((-FILEPROPERTY([name],'SpaceUsed'))/128.,2)) AS [Unused]

    FROM [sys].[database_files]

    ---
    SQLSlayer
    Making SQL do what we want it to do.

  • Mad-Dog

    SSCertifiable

    Points: 6099

    Adam Bean (4/27/2009)


    Steve Jones - Editor (4/27/2009)


    Are you sure there's free space in there? Perhaps there are temp objects preventing it from shrinking?

    Good call, run this to verify:

    USE [tempdb]

    SELECT

    [name]

    ,CONVERT(NUMERIC(10,2),ROUND(/128.,2)) AS [Size]

    ,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],'SpaceUsed')/128.,2)) AS [Used]

    ,CONVERT(NUMERIC(10,2),ROUND((-FILEPROPERTY([name],'SpaceUsed'))/128.,2)) AS [Unused]

    FROM [sys].[database_files]

    results

    name Size Used Unused

    tempdev 29221.25 4.19 29217.06

    templog 750.00 96.83 653.17

  • Lynn Pettis

    SSC Guru

    Points: 442091

    Run this and let us know the values for size.

    use [tempdb]

    go

    select * from sys.database_files

    select * from sys.master_files where database_id = 2

    go

  • Adam Bean

    One Orange Chip

    Points: 26440

    Yeah, lots of free space there ... but keep in mind, a data file will grow to it's size for a reason. Shrinking usually only results in the file growing back. Regardless, if you need to shrink, you need to shrink. I wouldn't shrink it all the way down ... usually good to leave some room in the tempdb. Actually you should match the amount of data files to the amount of CPU's for better performance.

    Anyways, run this:

    USE [tempdb]

    GO

    DBCC SHRINKFILE (N'tempdev' , 4096)

    GO

    Then re query your size. If nothing has changed, check the error log (sp_readerrorlog). See if there is anything about a wait or stall.

    ---
    SQLSlayer
    Making SQL do what we want it to do.

  • Mad-Dog

    SSCertifiable

    Points: 6099

    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 i 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 KB

  • ganci.mark

    Hall of Fame

    Points: 3482

    I am having the same trouble. My tempdb files will not shrink despite the fact that only a small portion is actually in use. There are no open transactions.

    tempdev 10006.94 15.38 9991.56

    templog 113.00 9.71 103.29

    tempdev2 7157.94 7.38 7150.56

    Shrinkdatabase, shrinkfile do not work.

    This query :

    select * from tempdb.sys.all_objects

    where is_ms_shipped = 0;

    Did produce 74 objects.

    It seems the only solution will be to bounce the instance.

    Does anyone have any other suggestions without restarting SQL Server?

    Thanks

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

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