December 7, 2011 at 8:21 pm
Comments posted to this topic are about the item Maximum number of Databases.
December 7, 2011 at 10:24 pm
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
December 7, 2011 at 11:27 pm
does anyone tested this.....? π
December 7, 2011 at 11:38 pm
: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
December 7, 2011 at 11:57 pm
Koen Verbeeck (12/7/2011)
:w00t: But not in production π
Good one, Can't think of doing it in prod π
M&M
December 8, 2011 at 1:16 am
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
December 8, 2011 at 1:58 am
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:
December 8, 2011 at 2:16 am
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
December 8, 2011 at 2:35 am
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 π
December 8, 2011 at 2:47 am
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
December 8, 2011 at 3:10 am
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! π
December 8, 2011 at 3:44 am
easy and good question!!!!
thanks!!!!
December 8, 2011 at 5:38 am
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
December 8, 2011 at 6:06 am
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!
December 8, 2011 at 7:10 am
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.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply