Why dbid is 1 for master, 2 for tempdb, 3 for model and 4 for msdb?
- Eirikur Eiriksson - Monday, November 20, 2017 12:11 PM
Then why are you bothering yourself and others if you do not have any answer on things properly? Who is prompting you to reply? We are here to discuss about our technical queries, if you want to share or through your daily frustration on things, go over somewhere else. NExt time before you reply to any post ensure you do know the answer. If you do not know then do not get over stressed, just leave commenting on the post. I am sure that you will know answers on other queries posted by other users and that is way you can increase your overall score to maintain your MVP status. Now take your BP pill and take rest!!
Thanks.
-
It may have to do with the order in which the databases are created when SQL Server is initially installed.
I can also see why SQL Server would want to consistently number the four system databases that are always present, because some scripts may depend on that numbering scheme."Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
- SQL-DBA-01 - Monday, November 20, 2017 12:15 PM
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day - J Livingston SQL - Monday, November 20, 2017 12:24 PMSQL-DBA-01 - Monday, November 20, 2017 12:15 PM
E tu Brute! :hehe: There went my spare keyboard
😎 - SQL-DBA-01 - Monday, November 20, 2017 11:47 AM
Wow, look who is upping their post count. Not all questions can be answered "By design." You were asked why you were interested in knowing why the first four system databases were numbered they way they are. I have been working with MS SQL Server for over 20+ years and not once did I ever ask that question nor have I been asked that question in an interview. It is just one of those things, by design, decided decades ago. I haven't seen anything in Microsoft documentation that explained why they were numbered the way they are nor do I see any reason to understand the reasoning just looking back over the years I have worked with the product. Just seems like trivial trivia.
-
Mr. Livingston, I love that animation ... :laugh:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
- SQL-DBA-01 - Friday, November 17, 2017 1:02 PM
Short answer: Someone assigned those numbers, many may years ago. Nobody ever thought there was a reason to change it. There is no significance to it, but every database needs to have a number and these were the numbers used,
Slightly longer answer: I could go all speculation now how someone said "we need a master database to store other databases", and then someone else said "we also need a place for temporary stuff". And then a few days later when they were thinking about user databases, the idea of a model database was born. And then it took maybe a few months until someone realized how useful it would be to store information about backups, and that master is not the right spot for it, so the msdb was born.
But that would be speculation. And highly irrelevant. One might equally well speculate that someone just threw a bunch of dice. Sometimes,someone just has to assign each thing a number and once the choice is made, it sticks.As a comment on the people who look at startup sequence in their log files: not relevant. The database ID is just an ID. As with any ID value, it is a number that has the property of being unique and no other property. There is no significance to it. These numbers are not assigned at startup (once a database is created, it will keep the same ID throughout its lifetime), and the startup order is not dictated by these numbers.
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/ - Jeff Moden - Monday, November 20, 2017 10:10 AM
Um....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - Eirikur Eiriksson - Monday, November 20, 2017 10:30 AM
Based on what I saw doing research for that article, TempDB clearing starts, and that triggers the startup of model.
It's easiest to see when one or both is damaged and can't start.Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - Jeff Moden - Sunday, November 19, 2017 12:20 PM
I know that I would have asked a similar question when I started out with computer systems. Heck, for that matter, would it be important that these numbers stay constant for each installation during reboots? For any installation? For any version? Sure, we can only guess as we aren't the server implementers, but it turns out that often integer ID's like that need to be consistent throughout a code base but that their order could be completely arbitrary, that it is enough that master be 1 and other databases NOT be 1, and in that code base, when we asked for database 1, it be the same database as the other time we asked for database 1. Or maybe its important that 1 actually be master?
Maybe that's the case here, maybe its not. I know that during one project of mine, I needed to create about 20 or 30 opcodes, but that internally I could not constantly use character strings to reference these opcodes but rather I assigned integers to them in sequence and the value of these integers were completely arbitrary but once they were assigned they needed to stay that way throughout the codebase. Even though I then would have to keep using these integers, I could still refer (and generate references) to these integers by using something like named constants, similar to #define's with the c macro processor for instance and while I did refer to them, they got changed to integers and the program itself did not have to use character strings to find an occurence of a data structure, a constant integer index was just fine.
Personally, I doubt SQL-DBA-01's question was life or death or even important enough to merit this conglomeration of threadizens, but as a fan of forum psychology I do get interested in what triggers these "gang ups". So tell me, what offended you guys this time? Can you determine where SQL-DBA-01 went wrong? Or was his offense more imaginary in nature? Curious minds wanna know!
- patrickmcginnis59 10839 - Monday, November 20, 2017 3:26 PM
The question itself wasn't the problem. All I could think of in response was that it was by design. I wasn't privy to the design decisions made decades ago so could not say more. I think it was more his responses to the various posts, almost like he wanted to provoke retorts.
- patrickmcginnis59 10839 - Monday, November 20, 2017 3:26 PM
Understood. A couple of us wanted to know why and not because we thought the question had an "as designed" answer. Some folks ask questions where they've got a bigger problem than the question they're asking but either don't know how to phrase the question or are, for some reason, embarrassed to ask the question about the real problem they're having. The only way to dig that info out is to ask them why they're asking.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Monday, November 20, 2017 3:41 PM
I think he was asking about the design, so "as designed" could be taken as a non response. Like for instance is there any significance to the numbering. Sometimes there is, sometimes there isn't.
He did answer you that it was out of curiosity, and while I'm not completely sure on parsing his English, I'm going to guess with his mention of architecture that he's probably curious about the innards like I often am, I know I went on a wild goose chase about why RBAR was so slow and it took a while for me to reason about it enough to satisfy my curiosity with reading and experimentation and at least in my case its made a world of difference about how I reason about how SQL Server is used and why.
And in Eirikur's case I know I'm struggling with what the OP was supposed to take from his posts, they were very confusing to me, and I think you chimed in on the OP simply because he typed:
"If you can't make anything out of it, then the best option is to ignore it."
yet by golly he really has a nugget of truth there. None the less, you are talking attitudes, instead of typing that, should he have rather apologised to Eirikur? Remember, Eirikur mentioned that he literally had no idea what the OP's goal was here, and the OP gave him some darn solid advice as a response and at this time you evaluated his response as a "poor attitude" and I'm going to respectfully disagree here, in fact were this forum moderated, Eirikur would have gotten my downvotes for a solid lack of contribution to the subject at hand despite posting multiple times, and indeed I'm going to side with the OP in that Eirikur did turn a technical question thread into personal attacks and frankly I'm sort of disappointed that nobody noted Eirikur's conduct in this thread. I shouldn't be, obviously, as long as I've participated here but I can't stop getting my hopes up I guess.
- patrickmcginnis59 10839 - Monday, November 20, 2017 6:33 PM
Yes. He did answer me and that was a good thing. I thought he might be having some other problem.
The following link shows what happens when we ask questions about WHY someone wants to do things. It didn't turn out so well on this link, though. I guess I can understand why the OP took exception to some of the remarks but at no time did anyone make fun of him for asking the question, which was, in fact, an interesting question.
https://www.sqlservercentral.com/Forums/FindPost1908835.aspx
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - SQL-DBA-01 - Monday, November 20, 2017 12:15 PM
I would like to agree with SQL-DBA-01 here, Eirikur, your posts are bad and you should feel bad, and I'd like to applaud SQL-DBA-01 and his generosity in taking the time to offer you some darn good advice in how to turn your posting around for the better of the site.
Viewing 15 posts - 31 through 45 (of 90 total)
You must be logged in to reply to this topic. Login to reply