Maximum number of Databases.

  • Jagadish Kumar Punnapu

    SSChasing Mays

    Points: 645

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

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

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

  • Koen Verbeeck

    SSC Guru

    Points: 258955

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

    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

    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

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

    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

  • martin.whitton

    SSCrazy

    Points: 2568

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

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

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

    _____________________________________________________________________
    MCSA SQL Server 2012

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    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

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

    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

    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 39 total)

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