Post reply

TEMPDB Space Issue in 2005

  • April 8, 2008 at 10:11 am

    #120851

    I have production DB on Sql2005/SP2 Ent.Ed.

    I do weekly Reindex using Job that has 4 steps for 4 major tables all similar to one below with diff table name

    ALTER INDEX ALL ON dbo.TRANSACT_Table

    REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON, ONLINE = ON);

    This one is the big one I know, this fills tempdb file upto 25 GB, I have 50 GB kept keeping this in mind.

    Sort in tempdb was choosen for reason, as I don't to fill my prod DB with extra unused space as I cannot shrink my big prod DB frequently.

    Problem starts after completion of this job I am planning to place a step

    to add tempdb shrink and bring size back to normal under 1 GB for rest of the week,

    I check space with following for tempdb

    select [FileSizeMB] = convert(numeric(10,2),sum(round(a.size/128.,2))),

    [UsedSpaceMB] = convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,

    [UnusedSpaceMB] =

    convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,

    [DBFileName] = isnull(a.name,'Total for all files')from

    sysfiles a group by a.name with rollup order by case when a.name is null then 2 else 1 end, a.name

    --OUTPUT

    FileSizeMBUsedSpaceMBUnusedSpaceMBDBFileName

    25408.00 5.5625402.44 tempdev

    1000.50 580.43420.07templog

    26408.50 585.9925822.51 Total for all files

    I tried Mgmt. studio Task --> Shrink DB --- No advantage other than additional 100+ MB free

    then tried T-Sql

    Backup Log Tempdb with truncate_only ---- Same outcome

    DBCC SHRINKFILE(tempdev,100)

    there are no Open Transactions.

    I am not sure why sql server is not able to release roughly 25 GB of space.

    Looking for some clue or solution from my sql expert friends. Thank you.

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

  • April 8, 2008 at 10:53 am

    #799897

    Do you want to try the following if not yet done?

    ALTER DATABASE tempdb MODIFY FILE

    (NAME = 'tempdev', SIZE = target_size_in_MB)

    Try the following link: http://support.microsoft.com/kb/307487

    Good luck!

  • April 8, 2008 at 11:11 am

    #799912

    You can also try.

    Backup log tempdb with no_log

    then

    DBCC Shrinkfile(tempdev , 100)

  • April 8, 2008 at 11:50 am

    #799931

    ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB)

    requires sql server restart so that's not an option in production server.

    Backup log tempdb with no_log is not the solution either.

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

Viewing 4 posts - 1 through 3 (of 3 total)

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