Maximum number of Databases.

  • Nice easy question.

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

    It depends on what the databases are. I know from experience that handling more than 8 is not a problem if they are good clean databases with sensible schema design, protected from ad-hoc queries/updates by solid logical security (only stored procs visible to apps, never tables or views) with business rules enforced whenever possible by keys and constraints (ie properly normalised) and designed with proper regard to modularity and separation of concerns (no data about the anatomy of fish in a database about stars, for example). On the other hand, I've known a single MIS database that was such a complex mess that it alone was too many databases for one person - and the mess was all caused by failure to do any of the common sense things I just mentioned.

    Tom

  • I would assume that system tables would be included in that....? My assumption on the limit is that SQL Server must use the smallint data type for the DB ID column, and thus the limit on DB numbers. And system DBs have a row in the sys.databases and a DB ID.

  • Very easy for my Thursday morning, thanks.

  • Well, this is weird, because in sys.databases DB ID is a straight up INT. So I wonder why smallint is the limit on databases then....

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

    Nice. I have some scripts to hide this in 😉

    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

  • It appears I have some testing to do.

    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

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

    I'd say eight's probably about right. BUT databases tend to be forgotten, forever backed up, and checked for errors. Only occasionally touched by unlinked websites, they fester like unloved take-out in the back of the fridge.

    How many databases can a developer handle? Yeah, eight. But they leave behind a trail of broken-hearted and forever wounded databases in their wake. Hopefully I'll never see thirty-two THOUSAND dbs on my systems... 'cause I normally clean up the unloved databases when I have to migrate them to new hardware.

  • 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

  • Revenant (12/8/2011)


    I am getting

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'GO'.

    on all 32,767 lines

    That's because GO is not a T-SQL keyword, but a batch seperator, recognised and processed by the client - when it is at the start of a line.

    (BTW, I think you don't really need to use seperate batches here, though having all 32K create db statements in a single batch might be a bit too much.)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (12/8/2011)


    Revenant (12/8/2011)


    I am getting

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'GO'.

    on all 32,767 lines

    That's because GO is not a T-SQL keyword, but a batch seperator, recognised and processed by the client - when it is at the start of a line.

    (BTW, I think you don't really need to use seperate batches here, though having all 32K create db statements in a single batch might be a bit too much.)

    Make sure to put them all on auto close & autoshrink.

    Then start foreachdb and check stuff in them :hehe:

  • Thanks, Hugo - I realized that a split second after I clicked Post Reply. :w00t:

    It is running now but it will take hours to finish.

  • tks for the question. i'm also curious to see how this works out in testing and if the number includes the system dbs also...

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

    I got this:

    Msg 1807, Level 16, State 3, Line 1

    Could not obtain exclusive lock on database 'model'. Retry the operation later.

    Msg 1802, Level 16, State 4, Line 1

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

  • Revenant (12/8/2011)


    I got this:

    Msg 1807, Level 16, State 3, Line 1

    Could not obtain exclusive lock on database 'model'. Retry the operation later.

    Msg 1802, Level 16, State 4, Line 1

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Very interesting. That's not at all what I would have expected. Did you look to see what was locking model, if anything?

  • cfradenburg (12/8/2011)


    Revenant (12/8/2011)


    I got this:

    Msg 1807, Level 16, State 3, Line 1

    Could not obtain exclusive lock on database 'model'. Retry the operation later.

    Msg 1802, Level 16, State 4, Line 1

    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Very interesting. That's not at all what I would have expected. Did you look to see what was locking model, if anything?

    I am looking into it, and I am re-running the test on a Datacenter installation.

Viewing 15 posts - 16 through 30 (of 38 total)

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