Temp DB size issue

Question

Post reply

Temp DB size issue

sandhyarao49

SSCrazy

Points: 2215
More actions
January 25, 2010 at 10:09 am

#138436

Hello,
I have increased the tempdb size by using the alter database statement to a higher value i.e 100 Gb.
But now i'm not able to reduce the size i'm geting the below error
"MODIFY FILE failed. Specified size is less than current size."
I shrink the file using the DBCC shrinkfile statement.
If i restated the server again Tempdb is rebuilding to old value 100 GB.
Can any one let me know how to proceed in this case as i want to reduce the size.
I'm using Developer edition 2005.
Regards,
Sandhya

Viewing 7 posts - 1 through 6 (of 6 total)

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

Firstly, why did you increase the size to 100gb?

You can alter the db file size to a value higher than the existing size.

DBCC Shrinkfile only reclaims the empty space to the OS.

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 2

Hi,

Thanks for the reply.

Is there any way to reduce the size of the temdb now ?

Henry Treftz SSCarpal Tunnel Points: 4207 More actions · Answer 3

Don't think so. To do most things with tempdb (like move it) you need to stop and start SQL Server since tempdb gets created at startup.

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

Check if moving tempdb to new location works..USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb.mdf'); --new location

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'D:\templog.ldf') --new location

You will need to restart SQL Server for this to take effect.

Thanks.

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 5

Right Click TempDB in SMMS, click Properties-->>Files, and see what is the initial Size for tempdev file. If it is 100GB( 102400 MB) or any number you dont like, change it to the number you want it to be. Then when you bounce the service, the data file size should be this initial size you set up.

DavidZahner Ten Centuries Points: 1169 More actions · Answer 6

It looks like this is the default file for your tempdb and not one that you added, if that is the case than you can use Method 1 outlined here: http://support.microsoft.com/kb/307487.