Posted by DickChristoph on 03/26/07 02:27

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

-Dick

"Icemokka" <icemokka@gmail.com> wrote in message
news:1174750705.655125.220160@p15g2000hsd.googlegroups.com...
> 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
>

[Back to original message]