Temp DB Size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Temp DB Size

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-06 : 09:57:25

I could not figure out, if this is a feature.
I set TempDB to autogrow, it accepts it but when I go back to it it is showing as fixed growth or max. size as 2GB.
Has anybody encountered that ?

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-02-06 : 11:01:57

I haven't. Are you using Express or a different version?

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-06 : 11:03:35

I am using express on my laptop and standard in two production servers, both places I have the exact same issue.

The notes on MS website are to the contrary saying that we can set it to autogrow.

I have one default file each for data and for log. because these are on C drive, I don't want them to auto grow to death.
So I usually add another data and a log file on a SAN Drive and set it to auto grow. That is where I fail with SQL 2005.

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-02-06 : 13:04:22

What are the results for this query?

select * from tempdb.sys.sysfiles

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-06 : 14:00:54

quote:
Originally posted by SQLServerDBA_Dan

What are the results for this query?

select * from tempdb.sys.sysfiles

fileid groupid size maxsize growth status perf name filename
1 1 93464 -1 0 2 0 tempdev C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
2 0 1280 -1 0 66 0 templog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
12 1 25600 -1 1280 2 0 templogx D:\SQL2005\log\templogx.ndf
14 1 25600 -1 12800 2 0 tempdevx D:\SQL2005\Data\tempdevx.ndf
15 0 2560 268435456 10 1048642 0 TempLogX2 D:\SQL2005\Log\TempLogX2.ldf
16 0 256 268435456 2560 66 0 Templogx3 D:\SQL2005\Log\Templogx3.ldf

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-02-06 : 14:21:29

quote:
Originally posted by pareshmotiwala

quote:
Originally posted by SQLServerDBA_Dan

What are the results for this query?

select * from tempdb.sys.sysfiles

fileid groupid size maxsize growth status perf name filename
1 1 93464 -1 0 2 0 tempdev C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
2 0 1280 -1 0 66 0 templog C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
12 1 25600 -1 1280 2 0 templogx D:\SQL2005\log\templogx.ndf
14 1 25600 -1 12800 2 0 tempdevx D:\SQL2005\Data\tempdevx.ndf
15 0 2560 268435456 10 1048642 0 TempLogX2 D:\SQL2005\Log\TempLogX2.ldf
16 0 256 268435456 2560 66 0 Templogx3 D:\SQL2005\Log\Templogx3.ldf

Here's what I see from your sys.files:

1 & 2 - fixed
12 - 10 mb autogrow with no max
14 - 100 mb autogrow with no max
15 - 10% autogrow with max 2,097,152 mb
16 - 20 mb autogrow with max 2,097,152 mb

Is that correct? Is that what you wanted?

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-06 : 15:03:34

the last 2 files were put to autogrow without limits.
It does not work on express and standard versions.
It defaults to max of 2,097,152...

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-02-06 : 15:41:11

quote:
Originally posted by pareshmotiwala

the last 2 files were put to autogrow without limits.
It does not work on express and standard versions.
It defaults to max of 2,097,152...

And you can change the other files?

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-06 : 16:12:18

If I add another data file, it lets me keep it in autogrowth with no max size limit.
but not the log files.

zntd
Starting Member

2 Posts

Posted - 2007-02-08 : 09:34:56

Are the settings changing after a reboot ? If so, then I have an ideal. As we know, TEMPDB is recreated everytime SQL starts. And, I think that I read somewhere that SQL uses the MODEL database as a template to create TEMPDB. So, try making your settings to the MODEL database, reboot, and see if the your desired settings to TEMPDB persist.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-12 : 14:57:07

Cannot add files to ModelDB per SQL2005.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-04-04 : 09:36:22

Ok, Problem solved.
The TempDB has a fixed limit of 2TB and not 2GB as I had mistakenly read.
The units of measurement were in MB not in KB.
My bad.
Regards
Paresh

Regards
Paresh Motiwala
Boston, USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources