TEMPDB Space Issue in 2005

Question

Post reply

TEMPDB Space Issue in 2005

Sameer Raval

Hall of Fame

Points: 3214
More actions
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

FileSizeMB UsedSpaceMB UnusedSpaceMB DBFileName

25408.00 5.56 25402.44 tempdev

1000.50 580.43 420.07 templog

26408.50 585.99 25822.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