Re: Maximum practical number of databases per SQL Server 2000 Inst

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Cesar (Cesar_at_discussions.microsoft.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 10:31:03 -0800

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

Relevant Pages

  • Re: More than 2G of Ram
    ... The wierd thing is that enterprise manager says it can go up ... to 2G since that is all standard edition can support. ... Sql Server could not spawn process_loginread thread ... > allow sql to use memory above 2G? ...
    (microsoft.public.sqlserver.server)
  • Re: Another Guess for Borlandīs new Corporate Roadmap
    ... The impedance match between OO and DBs isn't as much a language ... DBs reside on harddrives. ... for memory data, the opposit isn't true. ...
    (borland.public.delphi.non-technical)
  • Re: Why the performace differs?
    ... SQL Server is having the highest piority and consumes about 800MB ... from the memory (I made this by configure the SQL Server from the ... Enterprise Manager) ...
    (comp.databases.ms-sqlserver)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... we had no end of problems with SQL FTS. ... Looking for a SQL Server replication book? ... The Memory Usage and VM Size never increase over about 65 MB and 20MB. ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)