Maximum number of Databases.

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

  • 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

  • does anyone tested this.....? πŸ™‚

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

  • Koen Verbeeck (12/7/2011)


    :w00t: But not in production πŸ˜€

    Good one, Can't think of doing it in prod πŸ˜›

    M&M

  • 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

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

  • 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

  • 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 πŸ˜€

  • 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

  • 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! πŸ™‚

  • 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!
  • 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

  • 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!

  • 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