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

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

neurocon (Neurocon (AT) gmail (DOT) com) writes:
Autoclose is a database setting whereby SQL Server automatically shuts
down the database when the last user leaves. Subsequently if any user
starts to performa some action in the database, SQL Server has to start
up the database again.

Auto-close is an option that rarely is useful, as it causes more problems
that what it helps. However, if you as Sven have 6300 databases on the
server, the option starts to become compelling, as each open database
takes up memory.

You can use the ALTER DATABASE command to change the auto-close setting for
a database.

As for the original question, I don't really have any good suggestion.
It's not surprising that Mgmt Studio feels compelled to visit the database.
Probably the best is to avoind Management Plans altogether, but just set
up an SQL job that performs what you want to be done. Possibly you could
create a Maintenance plan for a small number of databases, and then
work with the plan in Business Intelligence Development Studio. (It is
ab SSIS package as I understand it.)

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

And honestly I'd probably question why anyone has 6300+ databases on a
single server.

And would go further to suggest if you're really doing that, you're probably
better off writing your own tools to administer it.

It's really an edge case....


--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

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 (AT) gmail (DOT) com> wrote

Greg D. Moore (Strider) (mooregr_deleteth1s (AT) greenms (DOT) com) writes:
A fellow MVP whose judgement I have very good faith in, told me of a client
that had 5000 databases on the same server. (And set to auto-close as I
recall.)

In that case it was some sort of an application provider, where each client
has its own database. And I would assume that the same is true in Sven's
case.

But you are right that you are probably best off gettings some tools to
administer that situations.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx