tempdb autogrow issue

Question

tempdb autogrow issue

zina13

Right there with Babe

Points: 797
More actions
January 25, 2005 at 9:33 am

#163171

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

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

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

zina13 Right there with Babe Points: 797 More actions · Answer 2

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

leifah Right there with Babe Points: 724 More actions · Answer 3

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

zina13 Right there with Babe Points: 797 More actions · Answer 4

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

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."

zina13 Right there with Babe Points: 797 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)