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
CesarExactly 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...
> 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
>|||Thanks for all of your suggestions.
The error I was getting was something like "Out of Memory". I think I know
how to fix it. I was creating thousands of small DBs and by default they
stay in mostly in memory and eventually my machine runs out of memory. When
I enabled the "AutoClose" property my small DBs, that flushes them from
memory and they reside entirely on the HD. That allowed me to create 10K DBs
in my test environment without any problems or SQL Server process to increase
in memory usage.
Thanks
Cesar
"Mike Epprecht (SQL MVP)" wrote:
> 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...
> > 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
> >
> >
>
>