471,570 Members | 973 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,570 software developers and data experts.

DB Maintenance Plan SQL 2005 on 6300+ Database

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

Mar 24 '07 #1
5 3397
On Mar 24, 8:38 am, "Icemokka" <icemo...@gmail.comwrote:
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

Mar 25 '07 #2
neurocon (Ne******@gmail.com) writes:
Could you clarify "auto-close"?
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, es****@sommarskog.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
Mar 25 '07 #3


"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...
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.)
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....

>
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
Mar 26 '07 #4
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" <ic******@gmail.comwrote in message
news:11**********************@p15g2000hsd.googlegr oups.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

Mar 26 '07 #5
Greg D. Moore (Strider) (mo****************@greenms.com) writes:
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....
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, es****@sommarskog.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
Mar 26 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Jim Andersen | last post: by
1 post views Thread by microsoft . public . sqlserver | last post: by
reply views Thread by Zvonko Bičkup | last post: by
reply views Thread by Neff | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.