[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

DB Maintenance Plan SQL 2005 on 6300+ Database

Icemokka

3/24/2007 3:39:00 PM

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
4 Answers

Jeje

3/24/2007 4:32:00 PM

0

have you try to create 1 script for all the DB where you do your maintenance
action^

or try to use the sp_msforeachdb procedure?

For example, try to generate a script file using the sp_msforeachdb
procedure, save the result in a file, and then execute this resulting
script.

sp_msforeachdb 'print ''use ?''
print ''go''
print ''some commands in the DB....''
'


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

Andrew J. Kelly

3/24/2007 4:40:00 PM

0

The Maintenance plans are not the most efficient and may try to read some
meta data that requires the db to be open first. I recommend that you create
custom scheduled jobs that issues the backups etc. in exactly the way you
want. I don't know what your requirements are but you may also want to look
into using some of the capabilities of the SAN to backup as well.

--
Andrew J. Kelly SQL MVP

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


TheSQLGuru

3/25/2007 11:01:00 PM

0

I have a client with essentially the same problem - 6100+ databases on a
SQL2005 cluster, growing at almost 10%/month in the last year! Every tool
except SSMS breaks down in some way or another when trying to connect up to
the server due to iteration through the database metadata. SQL Server's own
internal stuff (backups, maintenance plans, log shipping, database
mirroring, etc) doesn't function very well either with such large numbers of
databases.

I built a custom maintenance system which is driven by a single table that
contains suffient metadata to drive different management/maintenance
activities. Backups, index/statistics maint, report server refreshes
(hand-crafted log shipping to a report server without the internal overhead
of sql server's built-in log shipping) are all sproc driven from this one
table. VERY slick!! I would package it up and sell it if I thought there
were more than just the two of us with so many databases on one server.
:-))

I am concerned that you have your databases set for auto-close though. That
can be a SIGNIFICANT performance drain, especially if you have poor code
(bad ADO comes to mind) that does a lot of open/close connection stuff.
Also, connection pooling may be caught up in the mix here too. RAM is
cheap, and should NEVER be a reason to do bad things on your servers these
days!!

--
TheSQLGuru
President
Indicium Resources, Inc.

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


Dick in UK

3/27/2007 11:15:00 PM

0

Suggestions
1. have multiple instances. apart from segmenting [eg 16 instances of 400
db's] you will make better use of memory [if 32-bit CPU /3GB AWE etc]

2. use the SQL-Express [SQL Native Client] approach of AttachDBFileName
[check BOL or MS website]. This means db would not be permanently in the
sysdatabases table so maintplans would not iterate them.

HTH
Dick

"Icemokka" 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
>
>