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

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
Chai,
See "alter database" in BOL.
AMB
"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
>
>
|||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
"Chai" <chai@.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
>
sql

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

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.
ChaiChai,
See "alter database" in BOL.
AMB
"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 i
n
> 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
>
>|||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
"Chai" <chai@.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
>

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

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.
ChaiChai,
See "alter database" in BOL.
AMB
"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
>
>|||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
"Chai" <chai@.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
>

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
Johnny
Check out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny
|||How to Move TEMPDB from one location to another
--X--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny
|||Perfect. Thanks guys!
"Hari Prasad" wrote:

> How to Move TEMPDB from one location to another
> --X--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
> 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
> 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
>
>

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
JohnnyCheck out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
--
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||How to Move TEMPDB from one location to another
--­--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME ='E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME ='E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||Perfect. Thanks guys!
"Hari Prasad" wrote:
> How to Move TEMPDB from one location to another
> --­--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME => 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME => 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> > Hello,
> >
> > Does anyone know how I can successfully move the tempdb database to a
> > different drive besides C? I tried detaching and re-attaching it but you
> > can't do that with system databases.
> >
> > FYI, I am using SQL Server 2005.
> >
> > Thanks!
> > Johnny
>
>sql

How can I move tempdb database files out of C drive?

Hello,
Does anyone know how I can successfully move the tempdb database to a
different drive besides C? I tried detaching and re-attaching it but you
can't do that with system databases.
FYI, I am using SQL Server 2005.
Thanks!
JohnnyCheck out the Alter Database in BOL. For Tempdb you can specify a move
option for the files and it will take effect after a restart.
Andrew J. Kelly SQL MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||How to Move TEMPDB from one location to another
--_--
USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
'E:\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
'E:\templog.ldf')
go
where NAME refers to the logical name of the tempdb database and log files,
and where FILENAME refers to the new location of the
tempdb files. Once this command has run, you must restart the mssqlserver
service before it takes affect.
Thanks
Hari
SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
> Hello,
> Does anyone know how I can successfully move the tempdb database to a
> different drive besides C? I tried detaching and re-attaching it but you
> can't do that with system databases.
> FYI, I am using SQL Server 2005.
> Thanks!
> Johnny|||Perfect. Thanks guys!
"Hari Prasad" wrote:

> How to Move TEMPDB from one location to another
> --_--
> USE master
> go
>
> ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME =
> 'E:\tempdb.mdf')
> go
> ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME =
> 'E:\templog.ldf')
> go
> where NAME refers to the logical name of the tempdb database and log files
,
> and where FILENAME refers to the new location of the
> tempdb files. Once this command has run, you must restart the mssqlserver
> service before it takes affect.
> Thanks
> Hari
> SQL Server MVP
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:19307F29-9AF5-4499-8AC6-2A5E82040E6D@.microsoft.com...
>
>

How can I make tempdb to its original size?

Hi, everybody!
I installed MS SQL Server 2000, and add two files to tempdb.
And now I want to make tempdb to its original size by removing two files
which had beed added.
How can I do it?http://www.support.microsoft.com/?id=307487
Andrew J. Kelly
SQL Server MVP
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:uF0WdC1cDHA.2572@.TK2MSFTNGP11.phx.gbl...
>
> Hi, everybody!
> I installed MS SQL Server 2000, and add two files to tempdb.
> And now I want to make tempdb to its original size by removing two files
> which had beed added.
> How can I do it?
>
>