473,225 Members | 1,318 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 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 3457
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Mark S | last post by:
I have a db maintenance plan which is set to backup (then truncate hopefully) the transaction log. In order to backup a transaction log the db must be in single user mode so the maint. plan fails. ...
5
by: Jim Andersen | last post by:
Hi there, Used the wizard to create a maintenance plan for a db. But it fails most of the time. I checked the log-files. At 1.00 AM, it runs the "Optimizations" job for 6-9 seconds. It...
4
by: nielsonj1976 | last post by:
I am getting a failure on the db backup job of one of my maintenance plans. It is coming back with the generic error message of, "sqlmaint.exe failed. (Error 22029). The step failed." I...
2
by: Justin | last post by:
I am running a SQL maintenance job on a 40 GB database which performs optimizations by re-orginizing data and indexes pages. After the job is finished, a separate job peforming a SQL transaction...
1
by: microsoft . public . sqlserver | last post by:
I am using SQL server 7.0. After restoring all the databses (Except distribution) Everything seems to be working fine except the backup maintenance plan. I put following detail in the maintenance...
0
by: mmohammed | last post by:
Hi All, I am trying to Schedule a full backup and a transaction log backup in sql server 2005 using Maintenance Plan.My requirement is to schedule these two backups at different time say 10pm and...
0
by: Zvonko Bičkup | last post by:
Hi! When I try to create maintenance plan using wizard in SQL Management Studio for SQL Server 2005 Standard Edition, I get the following error: TITLE: Maintenance Plan Wizard Progress...
0
by: Neff | last post by:
I have a clustered installation of SQL Server 2005 running on Windows 2003 with SSIS installed on the cluster. I'm running SQL Server Management Server on one of the cluster nodes and I can...
4
by: fniles | last post by:
I have created a backup maintenance plan in SQL Server 2005. Is there any way to run/execute this SQL Server backup maintenance plan from VB.NET ? Thank you.
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.