Posted by neurocon on 03/25/07 15:42

On Mar 24, 8:38 am, "Icemokka" <icemo...@gmail.com> wrote:
> 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

Could you clarify "auto-close"? Do you mean that you are taking these
databases offline? If you have an offline database you cannot do
certain things via SSMS until the database is "online". I don't know
about changing the behavior of SSMS for this activity, but if you tell
me what information you're trying to obtain about the databases I
could probably tell you how to do it through tSQL. There is a chance
that if you're just looking for certain db level information that you
could query the same from sysobjects/tables. If you're looking to
setup a maintenance plan for a database then I'm assuming it must be
online for this activity. Can you give a bit more information about
specifically what you're trying to accomplish?

Chadd

[Back to original message]