Re: DB Maintenance Plan SQL 2005 on 6300+ Database

Hi

Try the "sp_databases" command in the Query Window of SSMS. Hopefully this
will enumerate the databases without opening them.

You can then put the output of sp_databases into a temp table and use a
cursor to go through each one and back it up or do whatever.

Prior to my current job I would wonder about the need for so many databases
but perhaps you have 6300 clients and for various legal reasons they don't
want their data in the same database as everyone else.

Good Luck

-***

"Icemokka" <icemokka@xxxxxxxxx> wrote in message
news:1174750705.655125.220160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

We have a SQL-cluster with over 6300 databases, most of them pretty
small in size and usage.
For this reason all these databases are on auto-close ( Otherwise SQL
would need many many gigabytes ).

We've recently upgraded to SQL 2005 because of the Management Studio,
because the EM is unusable on a SQL-Server with lots of databases.

But whenever you choose 'all user databases' or 'all databases' in any
of the DB Maintenance task, MSSQL begins starting up every database
which takes about 30 minutes. Until then, you cannot use your Studio
anymore.

My question : is there a possibility to avoid this behavior, it should
not go and check all those db's, just add my task.

PS : Detaching these databases is not an option ...

Thanks in advance,
Sven Peeters



.

Quantcast