Expend all DBs

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

"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

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 databa
se
> 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 duri
ng
> the evening hours.
>
> Ishu
>
>
>