July 22, 2005 at 12:53 pm
OK - this is probably a very basic question but...
I've recently set up a new SQL Server instance to host some Extranet databases (currently 15). I was just informed that I should expect continuing growth (as in number of new databases) beginning with an influx of probably 40-50 in the next several weeks. This growth will continue as the business expands.
Each database is less than 1gb is size and all will have very light usage with minimal growth.. So, my question is:
Is there some guideline as to how many databases can comfortably be placed into a single SQL Server instance?
Thanks.
July 22, 2005 at 3:49 pm
In some of my server i have 200+ databases on them.
mom
July 25, 2005 at 1:29 am
The artificial limit that SQL Server sets is 32,767 databases per instance of SQL Server. You should however stay far away from that. I have not seen any real guideline for this, that shows the effects of having X databases, but you will definitely run into problems with memory usage, open file handles and more. Take a look at this KB article: There may not be enough virtual memory with large number of databases in SQL Server 2000
Here is a quote from the end of the article:
"Microsoft recommends that you take serious consideration before you run a server with more databases than this [1500, as noted earlier in the article] because the overhead that is required for having this number of databases on the system is taking a lot of virtual memory away from the buffer pool, which may result in poor performance for the system as a whole."
If you will be having a log of databases, and they are really not used very often, you could consider using the autoclose option for them. Opening a database is however an expensive operation so if they are used little but 'all the time' that would be disastrous for performance. As always, test!
Finally, you should also note that Enterprise Manager will not be very easy to use with a lot of databases. See You may experience a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Serve for more info.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply