Maximum number of Databases.

Question

Maximum number of Databases.

Viewing 9 posts - 31 through 38 (of 38 total)

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

Cliff Jones SSChampion Points: 10547 More actions · Answer 1

Ninja's_RGR'us (12/8/2011)
Make sure to put them all on auto close & autoshrink.
Then start foreachdb and check stuff in them :hehe:

I like where this is headed.

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 2

Iulian -207023

SSCertifiable

Points: 7639

December 8, 2011 at 2:08 pm

#1418971

Quite a large number

Thank you for the question

Iulian

Revenant SSC-Forever Points: 42467 More actions · Answer 3

So here is the result, after 5:00:13 clean run on a Datacenter server:

Msg 1835, Level 16, State 1, Line 1

Unable to create/attach any new database because the number of existing databases has reached the maximum number allowed: 32766.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Revenant (12/8/2011)
So here is the result, after 5:00:13 clean run on a Datacenter server:
Msg 1835, Level 16, State 1, Line 1
Unable to create/attach any new database because the number of existing databases has reached the maximum number allowed: 32766.

Now that is what I call "GOOD NEWS".

Tom

Revenant SSC-Forever Points: 42467 More actions · Answer 5

I suppose, Tom, you noticed that the message says 32,766, not 32,767.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Revenant (12/8/2011)
I suppose, Tom, you noticed that the message says 32,766, not 32,767.

Is that because it is counting model in the total? So are there 32766 databases in addition to the system databases?

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

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

Revenant (12/8/2011)
Koen Verbeeck (12/7/2011)
: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 😀
I am getting
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GO'.
on all 32,767 lines

Yeah, I know 🙂

I got the error also, but I posted apparently the old unadjusted code.

Here is the corrected one:

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) + '; ' + CHAR(10) + 'GO'

FROM CTE_Numbers;

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

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Revenant (12/8/2011)
I suppose, Tom, you noticed that the message says 32,766, not 32,767.

I suspect that if it had said 32767 I wouldn't have commented.

Tom

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 9

Good easy question. Thanks.

http://brittcluff.blogspot.com/