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 migrate all objects alogwith all constraints,SP,Triggers etc from Development instance

Hi fellows,

I have to migrate all objects alogwith all constraints,SP,Triggers, indexes etc from Development instance to Production instance of a DB, all those things are created through wizard ie. Sql server 2000 Enterprise Manager. if i use DTS it only mirates data along with tables and views but constraints,SP,Triggers, indexes etc not yet copied.

can any body help me how can I solve this problem by copying all objects alogwith all constraints,SP,Triggers etc from Development instance to Production instance.

This is Sql server 2000 Cluster environment.

thanks in advance for any help

rahman

I would script all the objects out using Enterprise Manager and then run them all in the other environment.

-Jamie

|||

Dear Jamie

Thank you very much for your reply

would you like to give me a hence to write script for all the object's constraints (ie. Default,Check,Primary Key, Foreign Key etc) and indexes using Enterprise Manager.

I am new in SQL Server

Thanks in advance

|||

1. Right-click on an object.

2. Point at All tasks--> Generate SQL Script...

-Jamie

|||

Dear Jamie

Thanks for your reply.

I know this script only create table/view but not any constrains attached with it. As you know DTS also creates Table/views and imports the data too.

it'll not solve the problem, I m trying to write scripts for constraints getting info. from some views of Information_Schema.

|||

Thanks Jamie,

I didn't see the options available in Options Tab of All tasks--> Generate SQL Script

thank you very much

sql

How can I migrate all objects alogwith all constraints,SP,Triggers etc from Development inst

Hi fellows,

I have to migrate all objects alogwith all constraints,SP,Triggers, indexes etc from Development instance to Production instance of a DB, all those things are created through wizard ie. Sql server 2000 Enterprise Manager. if i use DTS it only mirates data along with tables and views but constraints,SP,Triggers, indexes etc not yet copied.

can any body help me how can I solve this problem by copying all objects alogwith all constraints,SP,Triggers etc from Development instance to Production instance.

This is Sql server 2000 Cluster environment.

thanks in advance for any help

rahman

I would script all the objects out using Enterprise Manager and then run them all in the other environment.

-Jamie

|||

Dear Jamie

Thank you very much for your reply

would you like to give me a hence to write script for all the object's constraints (ie. Default,Check,Primary Key, Foreign Key etc) and indexes using Enterprise Manager.

I am new in SQL Server

Thanks in advance

|||

1. Right-click on an object.

2. Point at All tasks--> Generate SQL Script...

-Jamie

|||

Dear Jamie

Thanks for your reply.

I know this script only create table/view but not any constrains attached with it. As you know DTS also creates Table/views and imports the data too.

it'll not solve the problem, I m trying to write scripts for constraints getting info. from some views of Information_Schema.

|||

Thanks Jamie,

I didn't see the options available in Options Tab of All tasks--> Generate SQL Script

thank you very much

How can I install Report Server without VS.NET?

VS.NET 2003 is required for the installation of the reporting services.
Question is how can I install the Reporting Server on a staging or
production box?
Thanks,
-StanVS.NET 2003 is ONLY required only for the report design components.
In your case, it sounds like you're only interested in installing the Report
Manager and RS Web Services, so you can install without VS.NET 2003. That
message you see when you go to install is only a warning that you will not
be able to use the design components without VS.NET 2003.
Best Regards,
Benjamin Pierce
"Stan" <nospam@.yahoo.com> wrote in message
news:OPmYMsmbEHA.4092@.TK2MSFTNGP11.phx.gbl...
> VS.NET 2003 is required for the installation of the reporting services.
> Question is how can I install the Reporting Server on a staging or
> production box?
> Thanks,
> -Stan
>