Maximum number of Databases.

  • Cliff Jones

    SSChampion

    Points: 10517

    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: 7508

    Quite a large number

    Thank you for the question

    Iulian

  • Revenant

    SSC-Forever

    Points: 42467

    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: 104772

    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

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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: 258955

    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: 104772

    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

    Good easy question. Thanks.

    http://brittcluff.blogspot.com/

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

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