How can I pre-allocate file size of TempDB in SQL 2000?

microsoft.public.sqlserver.server

1 May 2007 3:48 PM

Chai

Hello:

We reboot our production server on Mondays. I would like to pre-allocate
(set original) file size for TempDB to a reasonable size to avoid frequent
expand of this DB. How can I achieve that? I couldn't find any setting in
the Enterprise Manager to allow me to pre-allocate file size of TempDB.
What SQL command will I use then?

Thank you in advance for your help.

Chai

1 May 2007 4:52 PM

Alejandro Mesa

Chai,

See "alter database" in BOL.

AMB

Show quoteHide quote

"Chai" wrote:

> Hello:
>
> We reboot our production server on Mondays. I would like to pre-allocate
> (set original) file size for TempDB to a reasonable size to avoid frequent
> expand of this DB. How can I achieve that? I couldn't find any setting in
> the Enterprise Manager to allow me to pre-allocate file size of TempDB.
> What SQL command will I use then?
>
> Thank you in advance for your help.
>
>
> Chai
>
>
>

Are all your drivers up to date? click for free checkup

1 May 2007 5:12 PM

Kalen Delaney

Hi Chai

You can use ALTER DATABASE, similar to the following:

alter database tempdb
modify file
( NAME = tempdev,
SIZE = 10 MB)

You can run sp_helpdb to verify the name of the data file for tempdb, the
default is tempdev. You might also want to change the initial log size.

Note that you cannot set a file size with ALTER that is smaller than the
current size. So if you want the new size to be smaller that it is
currently, you will have to restart one more time and issue the alter before
tempdb gets too big.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

Show quoteHide quote

"Chai" <c***@trs.state.il.us> wrote in message
news:uXt%238gAjHHA.208@TK2MSFTNGP05.phx.gbl...
> Hello:
>
> We reboot our production server on Mondays. I would like to pre-allocate
> (set original) file size for TempDB to a reasonable size to avoid frequent
> expand of this DB. How can I achieve that? I couldn't find any setting
> in the Enterprise Manager to allow me to pre-allocate file size of TempDB.
> What SQL command will I use then?
>
> Thank you in advance for your help.
>
>
> Chai
>