MS SQL Server - Expend all DBs

Author

Expend all DBs

Tim

2005-05-31, 9:23 am

I support a server that has over 200 databases on 1 instance. Each database
is about 2 megs. Does anyone know how I can automate a script that will
increase the size to 500 megs for ALL DBs. I want to run this script during
the evening hours.

Ishu

Rick Sawtell

2005-05-31, 11:23 am

"Tim" <tim@nospam.com> wrote in message
news:Er_me.6218$sm1.512@news.cpqcorp.net...
> I support a server that has over 200 databases on 1 instance. Each
database
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script
during
> the evening hours.
>
> Ishu
>
>

First off, do you have enough storage to do this? That's about 1 terabyte
if my math is correct.

You could use dynamic SQL to do this.

Select the database name from master.dbo.sysdatabases into a cursor. For
each non-system database, run the ALTER DATABASE command and reset your
sizes.

Rick Sawtell
MCT, MCSD, MCDBA

SkyWalker

2005-05-31, 8:23 pm

There are couple of ways.
You can create a cursor where you will get
list of databases you need to expand
and then inside cursor use dynamic SQL to
run ALTER DATABASE against each DB.

Another way for example to run a script
that will generate another SQL script with names of DBs in there

Something like

select 'ALTER DATABASE '+name+'...' from master..sysdatabases where ... --
make sure you will exclude system databases fom there

Then execute result script to expand databases.
This way is good as a one time deal.

Regards.

"Tim" wrote:

> I support a server that has over 200 databases on 1 instance. Each database
> is about 2 megs. Does anyone know how I can automate a script that will
> increase the size to 500 megs for ALL DBs. I want to run this script during
> the evening hours.
>
> Ishu
>
>
>

Sponsored Links