January 25, 2005 at 9:33 am
Hi,
I have the following issue with tempdb autogrow parameter on the 2 of the servers (SQL2000 SP3a).
After the re-boot the autogrow parameter for tempdb is set to 1Mb (for both data file and trans log file).
Each time I am changing it to 10% (EM), but it happened again after the next re-boot.
Did anybody see that? How can I fix it,
TIA
Leonid Yakhkind
January 25, 2005 at 10:19 am
When tempdb is recreated at startup, it uses size and growth specified for the model database. To fix, set the model autogrow parameter to 10% before the next reboot.
Greg
Greg
January 25, 2005 at 11:00 am
The autogrow for the 'model' is 10% and is not changing after the re-boot.
The is a tempdb.sysfiles table and comparing the values in the status column for the 'normal' and 'abnormal' tempdb shows:
Normal tempdb: status=1048578 (data); status=1048642(ltrans log).
Abnormal tempdb: status=2 (data); status=66 (ltrans log).
From BOL : sysfiles.Status -
"Status bits for the growth value in either megabytes (MB) or kilobytes (K).
0x1 = Default device.
0x2 = Disk file.
0x40 = Log device.
0x80 = File has been written to since last backup.
0x4000 = Device created implicitly by the CREATE DATABASE statement.
0x8000 = Device created during database creation.
0x100000 = Growth is in percentage, not pages. "
It is not very clear for me...
Is it save to update status values ?
For production server?
Thank you,
Leonid
January 26, 2005 at 10:03 am
Privet! (?)
If your production server has sufficient space, why not make sure that SQL Server creates a large tempdatabase when the service starts, making the various autogrowth less relevant?
At my site, I set the DB size to 200MB, set autogrowth to TRUE and file growth to 50MB, instead of a percentage.
Restart service, and everything should be OK, I have never experienced problems with this. Forget about tempdb.sysfiles 🙂
Leif
January 26, 2005 at 10:52 am
Leif,
I agree that another schema of increment growing of databases could be better.
My concern was that after the reboot the increment for autogrow automatically re-set to 1 Mb (only on two servers out of 20 production servers) and how to fix it.
Thank you and Privet.
Leonid
January 26, 2005 at 11:17 am
As a part of a standard SQL server installation/configuration we always set the size and growth attributes of the master, msdb and tempdb databases. By doing this initially we've avoided the growth/fragmentation/reboot issues that are mentioned here. The size values for master and msdb are more than ample (it's worked for the 200+ instances I've managed over the last few years). However you may want to adjust the tempdb based on your own experience and monitoring. Most of the 19 instances I have now use the exact values in the script. But there are a couple of cluster instances with quite a few databases where the everything in the script for tempdb has been quadrupled ! I've found that few extra minutes spent altering these databases on the build has saved a lot of time later. Here's the script:
---
--- alter_system_dbs.sql - Rudyx
---
--- to be executed on all new SQL Server Installations
---
use master
go
---
alter database master modify file
(name = master,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
alter database master modify file
(name = mastlog,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
---
alter database msdb modify file
(name = MSDBData,
size = 50MB,
maxsize = 101MB,
filegrowth = 25MB)
go
checkpoint
go
alter database msdb modify file
(name = MSDBLog,
size = 25MB,
maxsize = 51MB,
filegrowth = 25MB)
go
checkpoint
go
---
alter database tempdb modify file
(name = tempdev,
size = 512MB,
maxsize = 1025MB,
filegrowth = 256MB)
go
checkpoint
go
alter database tempdb modify file
(name = templog,
size = 256MB,
maxsize = 513MB,
filegrowth = 256MB)
go
checkpoint
go
---
--- end of alter_system_dbs.sql
---
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 26, 2005 at 11:28 am
Thank you.
If anybody interested here are the microsoft article that I found while resiarching the issue:
PRB: File Growth Value for TempDB is Not Persistent When Changed From Fixed Increments to Percentage
http://support.microsoft.com/?kbid=816939
Leonid
January 26, 2005 at 10:04 pm
Autogrow = Fragmentation
As some have already said on this thread, set TempDB to be a reasonably large initial size and set autogrow to100 to 500 mb. We currently have a fixed size TempDB of 8 Gig and an autogrow of 500 mb. Fragmentation is very slight. Sounds like a lot for TempDB but have found some speed enhancements using TempDB that makes it all worthwhile. Our overall database is 400 gig so 8 gig really isn't that much.
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply