Why dbid is 1 for master, 2 for tempdb, 3 for model and 4 for msdb?
- SQL-DBA-01 - Friday, November 17, 2017 1:02 PM
Heck apparently its even different in Azure, go figure!
- patrickmcginnis59 10839 - Monday, November 20, 2017 3:26 PM
Very early in this thread Jeff answered "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?" to which OP replied "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."
I think this is where SQL-DBA-01 started to go wrong. - adish - Monday, November 20, 2017 7:44 PM
I think it was pretty straightforward tone wise, if a bit syntactically unfamiliar. I can understand however that the question itself is unusual, and maybe I can't be 100 percent positive about his tone, that's why I'd refrain from judging either way but I get your point.
-
Just in case anyone is interested in the side bar of which order the databases are loaded in, here's a list of the database loads on the last startup of my laptop. Entries chronologically start ascending from the bottom.
The order for that box is master, mssqlsystemresource, model, and then msdb. A bunch of user databases are loaded, then TempDB (the highlighted line) and then more user databases. That's a 2008 Developer's Edition.
On my prod box, which is the 2016 Enterprise edition, they look like this (kept the same bottom up order so as not to confuse and didn't print all the interceding database names).
... bunch of user databases here...
...bunch of user databases here...From the looks of it, master started and then just about everything else started up at the same second mark including all the system databases except TempDB and that started last but only 3 seconds later.
So my guess is that the numbering they used was as I said... they were numbered according to the order of their development rather than anything having to do with startup order or anything else that may be logical with the possible exception of the master database. I was actually a bit surprised on both machines to see master load before the mssqlsystemresource database but I'll leave that to someone that knows the internals.
--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 8:34 PMJust in case anyone is interested in the side bar of which order the databases are loaded in, here's a list of the database loads on the last startup of my laptop. Entries chronologically start ascending from the bottom.
The order for that box is master, mssqlsystemresource, model, and then msdb. A bunch of user databases are loaded, then TempDB (the highlighted line) and then more user databases. That's a 2008 Developer's Edition.
On my prod box, which is the 2016 Enterprise edition, they look like this (kept the same bottom up order so as not to confuse and didn't print all the interceding database names).
... bunch of user databases here...
...bunch of user databases here...From the looks of it, master started and then just about everything else started up at the same second mark including all the system databases except TempDB and that started last but only 3 seconds later.
So my guess is that the numbering they used was as I said... they were numbered according to the order of their development rather than anything having to do with startup order or anything else that may be logical with the possible exception of the master database. I was actually a bit surprised on both machines to see master load before the mssqlsystemresource database but I'll leave that to someone that knows the internals.
The load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old... - Jason A. Long - Monday, November 20, 2017 10:03 PM
You'd like the BlackArts01 and NastyFast01 databases. They contain the guts of databases used in presentations to demonstrate some really fast code. One of them, for example, demonstrates a 5 aggregate system across 7 million row (1040 bytes per row) of a 7GB table and uses one of the aggregates as a divisor for the others to produce 4 different temporal percentage aggregates by month for 7 years with 4 other columns ranking by month, month of year, year, and grand total... in about 4 seconds. Part of the ancillary learning in that presentation is how to truly use minimal logging to create the table with the clustered index already in place and it clearly demonstrates why you shouldn't necessarily delete "duplicate" indexes. It also demonstrates the fallacy of relying on a Clustered Index for performance especially on wide tables.
BlackArts01 was the precon that Ed Wagner and I did for Pittsburgh back in 2016 and featured parts of NastyFast01. It's also where I pointed out the extreme importance of the number "318" to every DBA in the world. 😉
--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 8:34 PM
Once master is started, multiple threads are spawned and the DBs are started up in parallel, assuming a normal startup
I don't know why I'm bothering to add more here though.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 - Jason A. Long - Monday, November 20, 2017 10:03 PMJeff Moden - Monday, November 20, 2017 8:34 PMJust in case anyone is interested in the side bar of which order the databases are loaded in, here's a list of the database loads on the last startup of my laptop. Entries chronologically start ascending from the bottom.
The order for that box is master, mssqlsystemresource, model, and then msdb. A bunch of user databases are loaded, then TempDB (the highlighted line) and then more user databases. That's a 2008 Developer's Edition.
On my prod box, which is the 2016 Enterprise edition, they look like this (kept the same bottom up order so as not to confuse and didn't print all the interceding database names).
... bunch of user databases here...
...bunch of user databases here...From the looks of it, master started and then just about everything else started up at the same second mark including all the system databases except TempDB and that started last but only 3 seconds later.
So my guess is that the numbering they used was as I said... they were numbered according to the order of their development rather than anything having to do with startup order or anything else that may be logical with the possible exception of the master database. I was actually a bit surprised on both machines to see master load before the mssqlsystemresource database but I'll leave that to someone that knows the internals.
The load order is far less interesting than the actual database names... "BlackArts01" & "NastyFast01..."
It's kinda like using the Kama Sutra to demonstrate how the Dewey Decimal System works, to a 15 year old...But more interesting than that is what the B is for in JBM :crazy:
Far away is close at hand in the images of elsewhere.
Anon. -
Like I said earlier, the id assignment of system databases simply results from the order in which the database are created during installation. It has nothing to do with startup. TEMPDB gets recreated at startup, but SQL Server reuses the same database_id of 2. The id of SSISDB, which isn't really a system database, can vary depending on when SSIS was installed, which could be after the user databases are created.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
- GilaMonster - Monday, November 20, 2017 11:53 PM
Understood on that. And apologies a bit.... You said that TempDb starts up just before Model and I missed the part where you were able to prove it. with something. I'd really be interested in seeing that because it seems contrary to the fact that if you have something you've added in the Model database (a Tally Table, for example), it will automatically appear in TempDb after a restart. I think the only way that could happen is if Model did fully start before TempDB.... either that or they copy to TempDB after they're both started and I think the wouldn't do it that way.
--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 - Tuesday, November 21, 2017 8:13 AM
tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
Also since tempdb has an id of 2 (right????) this contradicts the assertion that id's are assigned in the order that databases are created.
- Jeff Moden - Monday, November 20, 2017 11:38 PM
You should bring that to SQLSaturday #700, Jeff.
- patrickmcginnis59 10839 - Tuesday, November 21, 2017 8:29 AM
Actually, I don't think it occurs on startup as much as on shutdown.
I just shutdown my SQL Server 2014 DE on my laptop and the datetime on the files after shutdown were at shutdown time. - Lynn Pettis - Tuesday, November 21, 2017 8:58 AM
Hmm.... is that the last modified date or the created date though 🙂
- patrickmcginnis59 10839 - Tuesday, November 21, 2017 8:29 AM
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database
Also since tempdb has an id of 2 (right????) this contradicts the assertion that id's are assigned in the order that databases are created.
This is from a 2014 instance that was installed last year and has been re-started a few times. Looking at SYS.DATABASES, it's unclear in what order databases are initially created (or attached). However, MASTER would have to be initialized first, because it's responsible for keeping track of database_id for all other databases. I'm thinking it's .mdf file is probably distributed with installation media and attached due to the odd create_date. MODEL has the same create_date as MASTER. The create_date of MSDB seems to correlate with version of MSSQL; on a 2016 instance I see it's 2016-04-30 even though it was installed earlier in 2017.
The create_date on TEMPDB correlates to the last time the instance was re-started and gets updated every time. I'm thinking that TEMPDB would have to be created before MSDB, because SQL Server does some T-SQL configuration stuff, like setting up SQL Agent, that would require TEMPDB already in place.SELECT name, create_date, database_id
FROM sys.databases
WHERE database_id <= 4
ORDER BY create_date, database_id;name create_date database_id
master 2003-04-08 09:13:36.390 1
model 2003-04-08 09:13:36.390 3
msdb 2014-02-20 20:49:38.857 4
tempdb 2017-10-21 19:53:30.710 2"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 46 through 60 (of 90 total)
You must be logged in to reply to this topic. Login to reply