Maximum number of databases per instance

Question

Maximum number of databases per instance

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

patricklambin (2/27/2015)
Hi Nakul ,
Maybe a database is surely the object which "owns" the most important count of child objects ( 1 datafile , 1 logfile , a table with at least a column , that's to say 4 objects but it is difficult to use such a database).
This limit maybe exists only for one reason : how to retrieve easily a specific database in a list with 32768 names even if this list is sorted.

Good question, u may just press on the 1st letter of the db name, then it will navigate u to the series of db`s starting eith that letter and start searching :-D; but that might increase your performance headache anyway.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 2

Good 2 know that limitation, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Nakul Vachhrajani (2/27/2015)
Easy one.
What's interesting is that 32767 is the upper limit for SMALLINT (2-byte/16-bit integers). Yet, the database identifier has a data-type of INT, not SMALLINT. I guess that's it came from the days before SQL 2000?

Well, SQL Server 7 had the 32767 databases per instance limit too see https://msdn.microsoft.com/en-us/kb/aa933149.

So if it was ever different, it was before SQL 7.

All the SQL Server 6.5 and earlier documentation appears to have been removed from MSDN, so I can't tell whether that limit was ever less than 32767. I never used SQL Server 6.5, I worked with Oracle and Ingres and Postgres and various other non-microsoft DBMS back in those days, so I never knew what its limits were. Probably wouldn't have remembered it even if I had known it, it's so long ago.

Tom

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

TomThomson (3/1/2015)
Nakul Vachhrajani (2/27/2015)
Easy one.
What's interesting is that 32767 is the upper limit for SMALLINT (2-byte/16-bit integers). Yet, the database identifier has a data-type of INT, not SMALLINT. I guess that's it came from the days before SQL 2000?
Well, SQL Server 7 had the 32767 databases per instance limit too see https://msdn.microsoft.com/en-us/kb/aa933149.
So if it was ever different, it was before SQL 7.
All the SQL Server 6.5 and earlier documentation appears to have been removed from MSDN, so I can't tell whether that limit was ever less than 32767. I never used SQL Server 6.5, I worked with Oracle and Ingres and Postgres and various other non-microsoft DBMS back in those days, so I never knew what its limits were. Probably wouldn't have remembered it even if I had known it, it's so long ago.

Almost certain that it was 32,767 in SQL Server 6.5 and SQL Server 4.2 had 256

😎

Aishwarya_S SSChasing Mays Points: 606 More actions · Answer 5

For SQL SERVER 2014, the following are the specifications,

Filegroups per database:32,767

Filegroups per database for memory-optimized data: Not supported

Files per database:32,767

File size (data):16 terabytes

File size (log):2 terabytes

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I wonder if the answer is incorrect because the maximum number of databases in an instance is 32,767.

But you can create as many databases as you wish as long as you drop enough databases to avoid reaching the maximum.

Another answer would say that you can only create 32,763 because you won't create the system databases.

Of course, I'm just being silly here. :hehe:

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

March 3, 2015 at 8:33 am

#1781381

I wonder why not 32768.