Why dbid is 1 for master, 2 for tempdb, 3 for model and 4 for msdb?

Question

Post reply

Why dbid is 1 for master, 2 for tempdb, 3 for model and 4 for msdb?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
November 17, 2017 at 1:02 pm

#329616

Hi
Is there any reason why the below DBid order is maintained?
Master - 1
TempDB - 2
Model - 3
MSDB - 4
Thanks.

Viewing 15 posts - 1 through 15 (of 90 total)

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

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 1

ZZartin

SSC-Dedicated

Points: 30894

November 17, 2017 at 1:17 pm

#1968597

Why not?

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

ZZartin - Friday, November 17, 2017 1:17 PM
Why not?

Do you know the sequence is like this?

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

By design. Those are the four system databases that every instance of SQL Server has, makes since that they would have the first four database ids (1-4) and it makes since that master would be 1 and tempdb 2.

Now, why?

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Eirikur Eiriksson

SSC Guru

Points: 182882

November 18, 2017 at 9:36 am

#1968642

Ask Seagate
😎

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?

It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

Jeff Moden - Sunday, November 19, 2017 9:42 AM
SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?

Trying to comprehend the reason and the goal of this question, like questioning north south east and west, kilos and punds etc.
😎

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 7

Eirikur Eiriksson - Sunday, November 19, 2017 11:05 AM
Jeff Moden - Sunday, November 19, 2017 9:42 AM
SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?
Trying to comprehend the reason and the goal of this question, like questioning north south east and west, kilos and punds etc.
😎

If you can't make anything out of it, then the best option is to ignore it. 🙂

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

Lynn Pettis - Friday, November 17, 2017 2:11 PM
By design. Those are the four system databases that every instance of SQL Server has, makes since that they would have the first four database ids (1-4) and it makes since that master would be 1 and tempdb 2.
Now, why?

Lynn, I think based on your response all answer to any product related question would be that "By design". No? For example, lets say if there is any backup failed (random example), then the answer should be "by design" ...yes and that is because it is failing or any other question related to any product would be "by design". I liked your answer honestly.

Thanks.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 9

Jeff Moden - Sunday, November 19, 2017 9:42 AM
SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?

Jeff, I was eager to know why for tempDB it is 2 and why not it is 4. Jsut out of curiosity. If there is any reason in terms of architecture as concern. But anyways, seeing your answer looks like you never shown any interest to explore further on this. Hence I would not ask you any counter question. Thanks for replying to my question though. I feel pleased.

Thanks.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 10

SQL-DBA-01 - Sunday, November 19, 2017 11:34 AM
Eirikur Eiriksson - Sunday, November 19, 2017 11:05 AM
Jeff Moden - Sunday, November 19, 2017 9:42 AM
SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?
Trying to comprehend the reason and the goal of this question, like questioning north south east and west, kilos and punds etc.
😎
If you can't make anything out of it, then the best option is to ignore it. 🙂

That's a poor attitude. Why do you want to know this? Curiosity goes both ways here.

--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 SSC Guru Points: 1003857 More actions · Answer 11

SQL-DBA-01 - Sunday, November 19, 2017 11:38 AM
Jeff Moden - Sunday, November 19, 2017 9:42 AM
SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?
Jeff, I was eager to know why for tempDB it is 2 and why not it is 4. Jsut out of curiosity. If there is any reason in terms of architecture as concern. But anyways, seeing your answer looks like you never shown any interest to explore further on this. Hence I would not ask you any counter question. Thanks for replying to my question though. I feel pleased.

True enough. I've never had an interest in knowing why that particular numbering existed. Just wanted to know why you did. Thanks for your reply on that.

--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 SSC Guru Points: 1003857 More actions · Answer 12

SQL-DBA-01 - Sunday, November 19, 2017 11:36 AM
Lynn Pettis - Friday, November 17, 2017 2:11 PM
By design. Those are the four system databases that every instance of SQL Server has, makes since that they would have the first four database ids (1-4) and it makes since that master would be 1 and tempdb 2.
Now, why?
Lynn, I think based on your response all answer to any product related question would be that "By design". No? For example, lets say if there is any backup failed (random example), then the answer should be "by design" ...yes and that is because it is failing or any other question related to any product would be "by design". I liked your answer honestly.

Heh..,. more likely, it was a lack of pre-design. They probably decided that they needed Master first and TempDB second and then later decided that someone might want something to materialize in TempDB at bootup and so made Model 3rd but brought that database up second and, instead of changing numbering in the mass of code they'd already written, decided that the numbering really doesn't matter and so didn't change it.

It's probably similar to the idea that system SPIDs would always be 50 or less... they didn't plan on needing more. 😉

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 13

SQL-DBA-01 - Sunday, November 19, 2017 11:34 AM
Eirikur Eiriksson - Sunday, November 19, 2017 11:05 AM
Jeff Moden - Sunday, November 19, 2017 9:42 AM
SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?
Trying to comprehend the reason and the goal of this question, like questioning north south east and west, kilos and punds etc.
😎
If you can't make anything out of it, then the best option is to ignore it. 🙂

You better look up "persona non grata"
😎
but then again, you'll probably not get it

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 14

Eirikur Eiriksson - Sunday, November 19, 2017 1:11 PM
SQL-DBA-01 - Sunday, November 19, 2017 11:34 AM
Eirikur Eiriksson - Sunday, November 19, 2017 11:05 AM
Jeff Moden - Sunday, November 19, 2017 9:42 AM
SQL-DBA-01 - Friday, November 17, 2017 1:29 PM
ZZartin - Friday, November 17, 2017 1:17 PM
Why not?
Do you know the sequence is like this?
It's an interesting question that you ask, especially since the startup order is Master, Resource, Model, TempDB, and MSDB. My question is, why is it important to you?
Trying to comprehend the reason and the goal of this question, like questioning north south east and west, kilos and punds etc.
😎
If you can't make anything out of it, then the best option is to ignore it. 🙂
You better look up "persona non grata"
😎
but then again, you'll probably not get it

Sure. You also should away from Forums as you do not have the answers or eager to response of things. But each response counts you one mark, so continue to respond, I won't mind brother. Forum is to help on questions from others, but you better look up:

"persona non grata"
😎
but then again, you'll probably not get it

Thanks.