November 22, 2017 at 9:40 am
patrickmcginnis59 10839 - Tuesday, November 21, 2017 8:29 AMJeff Moden - Tuesday, November 21, 2017 8:13 AMGilaMonster - Monday, November 20, 2017 11:53 PMJeff Moden - Monday, November 20, 2017 8:34 PMFrom 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.
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.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.
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.
Understood on all of that and that's pretty much what I've been talking about (and I'm not actually talking about the order of IDs in this). Gail did some testing that shows that TempDB starts ever so slightly before the Model database and I'm wondering how that's possible because even TempDB relies on the Model database at startup and the fact that a Tally Table in the Model database will show up in TempDB after a restart seems to suggest contrary to Gail's good test and that the Model database must be fully started prior to TempDB starting.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2017 at 11:33 am
Jeff Moden - Wednesday, November 22, 2017 9:40 AMpatrickmcginnis59 10839 - Tuesday, November 21, 2017 8:29 AMJeff Moden - Tuesday, November 21, 2017 8:13 AMGilaMonster - Monday, November 20, 2017 11:53 PMJeff Moden - Monday, November 20, 2017 8:34 PMFrom 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.
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.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.
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.
Understood on all of that and that's pretty much what I've been talking about (and I'm not actually talking about the order of IDs in this). Gail did some testing that shows that TempDB starts ever so slightly before the Model database and I'm wondering how that's possible because even TempDB relies on the Model database at startup and the fact that a Tally Table in the Model database will show up in TempDB after a restart seems to suggest contrary to Gail's good test and that the Model database must be fully started prior to TempDB starting.
Not to be contrarian, well maybe a little, it is possible that tempdb starts before model and begins its initialization that can run in parallel with the starting of model. When it needs metadata from model it could be blocked until model completes its startup.
November 22, 2017 at 11:41 am
Lynn Pettis - Wednesday, November 22, 2017 11:33 AMJeff Moden - Wednesday, November 22, 2017 9:40 AMpatrickmcginnis59 10839 - Tuesday, November 21, 2017 8:29 AMJeff Moden - Tuesday, November 21, 2017 8:13 AMGilaMonster - Monday, November 20, 2017 11:53 PMJeff Moden - Monday, November 20, 2017 8:34 PMFrom 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.
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.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.
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.
Understood on all of that and that's pretty much what I've been talking about (and I'm not actually talking about the order of IDs in this). Gail did some testing that shows that TempDB starts ever so slightly before the Model database and I'm wondering how that's possible because even TempDB relies on the Model database at startup and the fact that a Tally Table in the Model database will show up in TempDB after a restart seems to suggest contrary to Gail's good test and that the Model database must be fully started prior to TempDB starting.
Not to be contrarian, well maybe a little, it is possible that tempdb starts before model and begins its initialization that can run in parallel with the starting of model. When it needs metadata from model it could be blocked until model completes its startup.
If tempdb isn't built from scratch, at the very least it needs the prior objects cleared out. It could also be that tempdb is built from scratch with a page by page copy (of some sort) from model, and maybe the tempdb file is opened, truncated, and then written with any number of possible operations by the software. Just theorizing out loud!
November 22, 2017 at 3:37 pm
Lynn Pettis - Wednesday, November 22, 2017 11:33 AMJeff Moden - Wednesday, November 22, 2017 9:40 AMpatrickmcginnis59 10839 - Tuesday, November 21, 2017 8:29 AMJeff Moden - Tuesday, November 21, 2017 8:13 AMGilaMonster - Monday, November 20, 2017 11:53 PMJeff Moden - Monday, November 20, 2017 8:34 PMFrom 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.
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.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.
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.
Understood on all of that and that's pretty much what I've been talking about (and I'm not actually talking about the order of IDs in this). Gail did some testing that shows that TempDB starts ever so slightly before the Model database and I'm wondering how that's possible because even TempDB relies on the Model database at startup and the fact that a Tally Table in the Model database will show up in TempDB after a restart seems to suggest contrary to Gail's good test and that the Model database must be fully started prior to TempDB starting.
Not to be contrarian, well maybe a little, it is possible that tempdb starts before model and begins its initialization that can run in parallel with the starting of model. When it needs metadata from model it could be blocked until model completes its startup.
I suppose it's possible... if that's the case, then the logs lie by more than 1 second. IIRC from Gail's previous post, she said that was the case but I sure would like to see the code/method she used to prove it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2017 at 9:03 am
master starts first, id is 1
tempdb is usually just before model, especially evident like Gail said if tempdb is not present as it copies tempdb from model, something which it cannot do if the files are already open.
MSDB is last
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 29, 2017 at 9:26 am
Perry Whittle - Wednesday, November 29, 2017 9:03 AMmaster starts first, id is 1
tempdb is usually just before model, especially evident like Gail said if tempdb is not present as it copies tempdb from model, something which it cannot do if the files are already open.
MSDB is last
Interesting... how is model used when creating a new database then?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2017 at 9:47 am
Jeff Moden - Wednesday, November 29, 2017 9:26 AMPerry Whittle - Wednesday, November 29, 2017 9:03 AMmaster starts first, id is 1
tempdb is usually just before model, especially evident like Gail said if tempdb is not present as it copies tempdb from model, something which it cannot do if the files are already open.
MSDB is lastInteresting... how is model used when creating a new database then?
To define this, you will have to try to connect to all the system db's about 1000 times a second when the service starts, absolutely futile though
😎
The log isn't neccesarely written in the right chronological order, take it with a pinch of "fleur de sel"
November 29, 2017 at 11:41 am
Perry Whittle - Wednesday, November 29, 2017 9:03 AMmaster starts first, id is 1
tempdb is usually just before model, especially evident like Gail said if tempdb is not present as it copies tempdb from model, something which it cannot do if the files are already open.
MSDB is last
It sounds like you can actually copy from model to tempdb with the tempdb files already open. This is a rather ordinary operation if the file handle is owned by the same process doing the open and then the copy.
edit: I don't mean a command prompt "copy" command, I mean a program copies content from a source file to a destination file, didn't want to waste another post in case there was some confusion there.
Back on SQL 2008 I did some research for an article and, iirc (it was a while ago), with some traceflags and some extra logging, what I saw was TempDB files opened, model opened and brought online, tempDB cleared (copied from model).
Really, I think Gail is describing exactly what happens here.
more edits:
To define this, you will have to try to connect to all the system db's about 1000 times a second when the service starts, absolutely futile though
I doubt even if SQL server opens tempdb before cleaning it out that it would allow a connection until it was ready to go. These guys are writing gianourmous complicated systems, I'd be surprised if they were open to creating race conditions like allowing a connection to tempdb before zapping it, they would probably rather leave the job of creating crap racy code to us end users 🙂
Someone could try something like strace except for windows. I haven't tried any of them but:
https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
Or this one looks interesting (haven't tried it)
November 29, 2017 at 1:41 pm
I was playing with it last week - used process monitor and some other trace flags. It does work how Gail had explained it.
I just followed the same process that Gail had used. The issue and Gail's original message about it is posted in one of the MS blogs. So you all need to learn how to search better as I found it last week. Play around with it, look through your logs, read the blog and all the pieces come together.
So now go find the article! 🙂
Sue
Edit: Fixed typo as I can search but I can't type
November 30, 2017 at 6:34 am
Sue_H - Wednesday, November 29, 2017 1:41 PMI was playing with it last week - used process monitor and some other trace flags. It does work how Gail had explained it.
I just followed the same process that Gail had used. The issue and Gail's original message about it is posted in one of the MS blogs. So you all need to learn how to search better as I found it last week. Play around with it, look through your logs, read the blog and all the pieces come together.
So now go find the article! 🙂Sue
Edit: Fixed typo as I can search but I can't type
So are you going to link it or are you just going to beat up on us poor folks who can't search? 🙂
November 30, 2017 at 8:31 am
patrickmcginnis59 10839 - Thursday, November 30, 2017 6:34 AMSue_H - Wednesday, November 29, 2017 1:41 PMI was playing with it last week - used process monitor and some other trace flags. It does work how Gail had explained it.
I just followed the same process that Gail had used. The issue and Gail's original message about it is posted in one of the MS blogs. So you all need to learn how to search better as I found it last week. Play around with it, look through your logs, read the blog and all the pieces come together.
So now go find the article! 🙂Sue
Edit: Fixed typo as I can search but I can't type
So are you going to link it or are you just going to beat up on us poor folks who can't search? 🙂
It's not as hard to find as you'd think. I vaguely knew some of what was posted but couldn't remember specifics - why or what trace flags. So I just searched on the ones I knew had an impact in one way - just search on: 3608 3609 tempdb
You'll find the blog. But the other interesting article that comes up and compliments the whole thing is this one:
The SQL Server Instance That Will not Start
That one is also Gail's article but if you play around with things similar to what she writes about, you get a way better idea about the startup process. And then if you search on it further, you can find quite a few posts around some of it that are less than accurate. That's the PC term, I wanted to say something else.
Both model and tempdb are needed to start up all other databases (not master of course) and it looks like model needs tempdb and tempdb needs model. The order of what gets logged with "starting up" doesn't mean that database is recovered, it doesn't mean nothing happened with the database before that and it just doesn't seem to be as important as it's made out to be.
Sue
November 30, 2017 at 9:03 am
Sue_H - Thursday, November 30, 2017 8:31 AMpatrickmcginnis59 10839 - Thursday, November 30, 2017 6:34 AMSue_H - Wednesday, November 29, 2017 1:41 PMI was playing with it last week - used process monitor and some other trace flags. It does work how Gail had explained it.
I just followed the same process that Gail had used. The issue and Gail's original message about it is posted in one of the MS blogs. So you all need to learn how to search better as I found it last week. Play around with it, look through your logs, read the blog and all the pieces come together.
So now go find the article! 🙂Sue
Edit: Fixed typo as I can search but I can't type
So are you going to link it or are you just going to beat up on us poor folks who can't search? 🙂
It's not as hard to find as you'd think. I vaguely knew some of what was posted but couldn't remember specifics - why or what trace flags. So I just searched on the ones I knew had an impact in one way - just search on: 3608 3609 tempdb
You'll find the blog. But the other interesting article that comes up and compliments the whole thing is this one:
The SQL Server Instance That Will not StartThat one is also Gail's article but if you play around with things similar to what she writes about, you get a way better idea about the startup process. And then if you search on it further, you can find quite a few posts around some of it that are less than accurate. That's the PC term, I wanted to say something else.
Both model and tempdb are needed to start up all other databases (not master of course) and it looks like model needs tempdb and tempdb needs model. The order of what gets logged with "starting up" doesn't mean that database is recovered, it doesn't mean nothing happened with the database before that and it just doesn't seem to be as important as it's made out to be.Sue
awesome thanks!
November 30, 2017 at 10:29 am
Jeff Moden - Wednesday, November 29, 2017 9:26 AMPerry Whittle - Wednesday, November 29, 2017 9:03 AMmaster starts first, id is 1
tempdb is usually just before model, especially evident like Gail said if tempdb is not present as it copies tempdb from model, something which it cannot do if the files are already open.
MSDB is lastInteresting... how is model used when creating a new database then?
Ever tried viewing model or its properties while a database is being created.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 30, 2017 at 10:30 am
Perry Whittle - Thursday, November 30, 2017 10:29 AMJeff Moden - Wednesday, November 29, 2017 9:26 AMPerry Whittle - Wednesday, November 29, 2017 9:03 AMmaster starts first, id is 1
tempdb is usually just before model, especially evident like Gail said if tempdb is not present as it copies tempdb from model, something which it cannot do if the files are already open.
MSDB is lastInteresting... how is model used when creating a new database then?
Ever tried viewing model or its properties while a database is being created.
Nope
November 30, 2017 at 10:46 am
Perry Whittle - Thursday, November 30, 2017 10:29 AMJeff Moden - Wednesday, November 29, 2017 9:26 AMPerry Whittle - Wednesday, November 29, 2017 9:03 AMmaster starts first, id is 1
tempdb is usually just before model, especially evident like Gail said if tempdb is not present as it copies tempdb from model, something which it cannot do if the files are already open.
MSDB is lastInteresting... how is model used when creating a new database then?
Ever tried viewing model or its properties while a database is being created.
No. What happens?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 76 through 90 (of 90 total)
You must be logged in to reply to this topic. Login to reply