Maximum number of Databases.

Question

Maximum number of Databases.

Jagadish Kumar Punnapu

SSChasing Mays

Points: 645
More actions
December 7, 2011 at 8:21 pm

#261080

Comments posted to this topic are about the item Maximum number of Databases.

Viewing 15 posts - 1 through 15 (of 38 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Easy Peezy - thx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Pieter de K SSC Veteran Points: 259 More actions · Answer 2

Pieter de K

SSC Veteran

Points: 259

December 7, 2011 at 11:27 pm

#1418480

does anyone tested this.....? 🙂

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

:w00t: Easy one! Thanks!

It should be easy to test.

WITH CTE_Numbers (number)

AS

(SELECT ROW_NUMBER() OVER (ORDER BY sc1.name) AS number

FROM master.sys.columns sc1, master.sys.columns sc2)

SELECT TOP 32767 'CREATE DATABASE Test' + CONVERT(VARCHAR(6),number) + '; GO'

FROM CTE_Numbers;

Execute the code and get the results as text. Copy paste into a query window and hit F5. But not in production 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

M&M SSC-Insane Points: 21700 More actions · Answer 4

Koen Verbeeck (12/7/2011)
:w00t: But not in production 😀

Good one, Can't think of doing it in prod 😛

M&M

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

Why not in prod?

Don't you have 82 GB left for 32,767 empty Databases? 😉

I tried it with 1001 though (on my local machine of course!) and at these 1001 DBs were created successfully.

Interestingly, the NOWAIT option in RAISERROR does not seem to work anymore after approx. 500 loops:

DECLARE @a INT = 0;

WHILE @a < 10000 BEGIN -- WHILE

SET @a+=1;

WAITFOR DELAY '00:00:00.030';

RAISERROR('%d',10,1,@a) WITH NOWAIT;

END -- WHILE

Starting with the output of 500, the messages are getting buffered again (50 messages per flush).

Best Regards,

Chris Büttner

philip.cullingworth SSCrazy Points: 2150 More actions · Answer 6

Koen Verbeeck (12/7/2011) But not in production 😀

What could possibly go wrong with running code from SQL Server Central on the production server?

........ oh yes, I remember .......

.... it can stop the SQL Server service :blush: :Whistling:

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Christian Buettner-167247 (12/8/2011)
Why not in prod?
Don't you have 82 GB left for 32,767 empty Databases? 😉

I do. It is the angry DBA I am worried about 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Louisa Whitton SSCrazy Points: 2577 More actions · Answer 8

Just out of interest, does anyone know what would happen if you did try to create the 32768th database?

Is there an error message for just this occurrence?

And, no, Philip, please don't try to find out by testing it 😀

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 9

martin.whitton (12/8/2011)
Just out of interest, does anyone know what would happen if you did try to create the 32768th database?
Is there an error message for just this occurrence?
And, no, Philip, please don't try to find out by testing it 😀

And another question out of curiosity:

are the 4 system databases included in this number?

In other words, can you only create 32763 user databases?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Richard Warr SSCertifiable Points: 6963 More actions · Answer 10

A better question would be "What is the maximum number of databases a developer can handle?". I've got 8 open at the moment and in danger of losing track of what does what! 🙂

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 11

easy and good question!!!!

thanks!!!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Thomas Abraham SSChampion Points: 10761 More actions · Answer 12

Richard Warr (12/8/2011)
A better question would be "What is the maximum number of databases a developer can handle?". I've got 8 open at the moment and in danger of losing track of what does what! 🙂

Depends on the databases ... and the developer ... but most importantly, on the nature of the customers/business. 🙂

OP: Thanks for the question!

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

GregoryF SSCertifiable Points: 6296 More actions · Answer 13

Interesting question, though entirely triva as in the real world this is *so* unlikely to ever be tested. It's kind of like what is the max size for a DB (512TB), no orginization wiould run production using SQL as such numbers, they are just theoretical maximums provided by Microsoft.

Seriously as to the question, scale out. There would be too much administrative hadssle running 10,000's of db's on a single instance

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 14

I can't imagine this many databases on a single server, or for that matter in a single company. It would be an interesting experiment to load up a single instance and then put each db under moderate load. At what point would the load overwhelm the server or db engine. What would go first, IO, Memory, network interfaces. I doubt I will ever know.