December 8, 2011 at 7:12 am
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
December 8, 2011 at 7:29 am
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.
December 8, 2011 at 7:30 am
Very easy for my Thursday morning, thanks.
December 8, 2011 at 7:30 am
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....
December 8, 2011 at 8:49 am
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
December 8, 2011 at 8:52 am
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
December 8, 2011 at 9:15 am
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.
December 8, 2011 at 9:23 am
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
December 8, 2011 at 9:27 am
Revenant (12/8/2011)
I am gettingMsg 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.)
December 8, 2011 at 9:30 am
Hugo Kornelis (12/8/2011)
Revenant (12/8/2011)
I am gettingMsg 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:
December 8, 2011 at 9:35 am
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.
December 8, 2011 at 9:40 am
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...
December 8, 2011 at 9:57 am
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.
December 8, 2011 at 11:51 am
Revenant (12/8/2011)
I got this:
Msg 1807, Level 16, State 3, Line 1Could 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?
December 8, 2011 at 12:33 pm
cfradenburg (12/8/2011)
Revenant (12/8/2011)
I got this:
Msg 1807, Level 16, State 3, Line 1Could 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