| Author |
Topic |
|
sschwarze
Starting Member
15 Posts |
 Posted - 2003-03-25 : 08:59:08
|
| I am a looking for documentation that says how many databases are allowed on a SQL Server instance. We are having lockups from time to time and the networking people tell us that we can have NO More than 20 databases(no matter what size) per SQL instance. We currently have 60 databases with the total size of data + logs being 36.6 GB of a 67 GB partition. We are running a cluster of Compaq servers with 1.5 gb of ram with dual processors at 1 mhz each on each half on the cluster and we useing load balancing. I AM NOT A SERVER PERSON so forgive the crudeness of my spec list. Please point me in the correct direction. IF it is not in black and white from Microsoft they will not believe me. Thank You!!! |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-03-25 : 09:11:25
|
| In Books Online (the SQL Server 'help' file) search for "maximum capacity specifications"(and with SQL2K you can have 32767 databases per instance) |
 |
|
|
sschwarze
Starting Member
15 Posts |
Posted - 2003-03-25 : 09:35:23
|
| Thank you so much!! It is just the smoking gun I need! I LOVE THIS PLACE! I am not sure how the any developer can do his job without SQLTEAM.COM. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-03-25 : 11:13:41
|
| Well theoretically yes, you could create upto 32,767 databases on a server. But then, you must consider a zillion other factors, not the least of which are Hard Disk performance, RAM, processor speed, number of processors etc. I cant imagine 60 databases being accessed from a few disks!!!OS |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-25 : 12:26:14
|
| Well I would say that 60 databases is probably too much even though SQL Server allows way more databases than that. Have you tried running SQL Profiler and the System Monitor to determine what the cause is? If you need help on this, just let us know.Tara |
|
|
|
RoyalSher
Yak Posting Veteran
95 Posts |
Posted - 2003-03-25 : 23:36:14
|
quote:
Thank you so much!! It is just the smoking gun I need! I LOVE THIS PLACE! I am not sure how the any developer can do his job without SQLTEAM.COM.
quote:
If you need help on this, just let us know.
I am totally obliged for your contribution and helping hand.. and totally agree with schwarze.Cheers to all members here.. especially Rob Volk - Gr8 Job!! RoyalSher.The world is the great gymnasium where we come to make ourselves strong. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-03-25 : 23:56:02
|
quote:
In Books Online (the SQL Server 'help' file) search for "maximum capacity specifications"(and with SQL2K you can have 32767 databases per instance)
Considering that the data type for the dbid column in sysdatabases is smallint, I wonder why they limited it to only the unsigned range and not the signed range....You will never get 32,767 databases simply because system databases (master, model, msdb and tempb) take dbid's 1,2,3 and 4.Oh and for other nerd related news..The column dbid is a sequential number and SQL Server is smart enough to find any gaps.. eg. Drop Northwind database (dbid of 6) and create a new database.. you will find that it has a db of 6.DavidM"SQL-3 is an abomination.." |
|
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-03-26 : 02:37:51
|
quote:
You will never get 32,767 databases simply because system databases
[really pedantic mode]but the system databases are databases, so technically speaking you can have 32767 databases[/really pedantic mode][reality mode]Of course I know you will not have 32767 db's on one server, however I've been with a (cheap) host which had ~ 65 databases on one instance and it worked surprisingly well.You can't say by definition 60 is too many, as said, there are a lot more things to consider. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2003-03-26 : 11:09:33
|
| I am not sure if MS will be happy. But I have a server where we are running 49 instances each worth about 1-2 gb. SOme of them even 2-10 gb. the memory is only 4gb. So don't worry too much about the spec.... |
|
|
|
|
Database Count per server