maximum databases no.

hi,
I would like to hear from you guys how many databases (maximum number)
did you reach on a single server.
We are trying to work with a few hundreds of databases at the same
time, and we get weird errors or problems, even with low load on these
databases.
I would like to hear from your experience and maybe get leads to
material on the subject.
I'm aware of the official limits (http://technet.microsoft.com/en-us/
library/ms143432(SQL.90).aspx) but in reality the numbers are much
lower.
I don't have any problems with over 500 databases on two different
production systems.
Of course, I took painstaking efforts to make sure the systems were designed
to really treat the databases as independently as possible.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegro ups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>
|||I have a client with 6400+ databases on a single relatively low-end cluster
(2 dual core AMD 64 bit CPUs, 8GB ram, VERY low-end drive system). These
same databases are log-shipped (custom-code, not built in) to an even
wimpier stand alone server for reporting purposes. Issues include:
1) Most automated tools break down. These include automated
monitoring/backup apps as well as tools like DBArtisan, which simply barf at
the extreme amount of system information they try to chew through.
2) Log-shipping/mirroring/replication become essentially non-useable too in
my experience.
3) Automation of things like rollouts, maintanence, etc are a must to keep
your sanity. I had a developer build what I call a "database shotgun" that
uses between 10 and 20 threads to concurrently run scripts against multiple
databases at a time. Stuff that takes ms_foreachdb tens of minutes to
complete now complete in a few minutes.
4) msdb will get huge. plan for this by expanding the data and log file
sizes ahead of time to avoid fragmentation. Set up jobs to flush old
history records for job runs/backups as appropriate.
Not sure about the 'wierd errors or problems' you are receiving. Any
examples?
TheSQLGuru
President
Indicium Resources, Inc.
<yaniv.harpaz@.gmail.com> wrote in message
news:1181232580.249349.26550@.q69g2000hsb.googlegro ups.com...
> hi,
> I would like to hear from you guys how many databases (maximum number)
> did you reach on a single server.
> We are trying to work with a few hundreds of databases at the same
> time, and we get weird errors or problems, even with low load on these
> databases.
> I would like to hear from your experience and maybe get leads to
> material on the subject.
> I'm aware of the official limits (http://technet.microsoft.com/en-us/
> library/ms143432(SQL.90).aspx) but in reality the numbers are much
> lower.
>
|||with 64-bit 8GB memory I got connection timeouts after going over 200
connections
I got very efficient queries starting to take 10 minutes instead of 2
seconds (without having high load on the DB server)
|||yaniv.harpaz@.gmail.com wrote:
> with 64-bit 8GB memory I got connection timeouts after going over 200
> connections
> I got very efficient queries starting to take 10 minutes instead of 2
> seconds (without having high load on the DB server)
I have to second yaniv's experience with sql2005 - with time it becomes
much more brittle than Sql2000.