Maximum practical number of databases per SQL Server 2000 Instance

Does anyone know what the maximum practical number of databases that could be
created and used per a SQL Server 2000 Enterprise Instance. I know that the
theoretical limit in BooksOnLine is 32K. But my application is dying way
before that.
Thanks in advance
Cesar
Exactly what is dying? It's usually not the number of databases but more
what you are doing with them and how you have your resources configured.
Andrew J. Kelly SQL MVP
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||We have hundreds of databases on our cluster. It creates some interesting
issue (starting and stopping the service takes a looooong time, for
example). But it runs pretty smoothly, all things considered.
Bob Castleman
SuccessWare Software
"Cesar" <Cesar@.discussions.microsoft.com> wrote in message
news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
> Does anyone know what the maximum practical number of databases that could
> be
> created and used per a SQL Server 2000 Enterprise Instance. I know that
> the
> theoretical limit in BooksOnLine is 32K. But my application is dying way
> before that.
> Thanks in advance
> Cesar
|||Hi
There are some KB articles on what needs to changed in SQL Server for it to
work well with more than 1'000 DB's. It comes down to having enough memory
available for the file structures. I don't have the KB at hand.
The more DB's you have, the more difficult it is to manage them with
Enterprise Manager. And one big issue, they all share TempDB, so if the
applications are written badly, you have a nice bottleneck.
I have tested to 600 DB's. Shutdown does take a while, and so does startup.
Enterprise Manager took 32 seconds to render the screen.
If I were you, and it is possible in your scenario, I would run multiple
instances with about 100 DB's per instance. Then, applying a SP or a hotfix
does not affect every DB, neither does a failure.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Castleman" <nomail@.here> wrote in message
news:#xuo4wu4EHA.2124@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> We have hundreds of databases on our cluster. It creates some interesting
> issue (starting and stopping the service takes a looooong time, for
> example). But it runs pretty smoothly, all things considered.
> Bob Castleman
> SuccessWare Software
> "Cesar" <Cesar@.discussions.microsoft.com> wrote in message
> news:1FCBB476-FEC0-493C-AA99-D7BF6E867B84@.microsoft.com...
could[vbcol=seagreen]
way
>