| Author |
Topic |
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
 Posted - 2003-05-01 : 12:55:28
|
| SQL server 2K started with -T3608 parameter.tempdb detached....Now tempdb can't be attached because model not activated, model not activated because of -T3608 switch. Try start SQL w/o the -T3608 paramter and SQL fails to start - can't find DB id 2 (i.e. tempdb!).What now ??*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 13:23:01
|
| Are you using -T3608 so that you can move the model database? If not, please explain why you are using -T3608.Please see this article:[url]http://support.microsoft.com/default.aspx?kbid=224071[/url]Tara |
 |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-01 : 13:26:13
|
| yes ... the -T3608 switch was in to move model, msdb. Unfortunately, instead of running with the modify file as per article, tempdb was detached ... and that where the sh-one(1)-t started.DEEEEEEP sh-1-t*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 13:28:47
|
| Have you tried rebuilding master yet? Are you even able to rebuild master at this point or is it not able to get to this point? If it can't, then you are going to need to reinstall SQL Server, then restore your databases.Tara |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-01 : 13:36:59
|
| tried rebiuldm.It all looked so nice, configuring server, until it ended with:Error -1.THAT'S IT! Have these people heard of error messages ?At this point , uninstall and re-install ? Is there NO WAY around this ?We get frustratingly close ! Basically, we can attach tempdb, but it becomes db id 7, not 2, and then is useless. We can start SQL, but only with -T3608 setting, which means we cannot activate model, which means tempdb doesn't get rebuilt (afaik).Anyone know of a way to activate model from a point where SQL has started with the -T3608 parameter specified ?Without tempdb, things like sp_reconfigure don't work !AAARGH!*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 13:40:01
|
quote:
We get frustratingly close ! Basically, we can attach tempdb, but it becomes db id 7, not 2, and then is useless.
If you can attach tempdb, then you can go back and change the dbid to 2 although this is not recommended but it maybe all that you can do without reinstalling. You will need to allow updates to the system catalog to do this, then just update sysdatabases and whatever other tables contain dbid (do you have an ERD of the system tables from MS to determine which tables contain dbid?). I'm not sure that this will work, but it certainly is worth a try considering your situation.Tara |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-01 : 14:11:26
|
| Sadly, that's what I was referring to when I mentioned that sp_reconfigure won't run w/o a tempdb. we can get the sp_configure, "allow updates" to 1 (i.e. allow update of system database'.other idea, since we can't do this, is:gonna try and hack by taking nice clean master (another server), attaching droken master as user db, chacking sysdatabases on "user_master", then trying to start server with hacked master ...chances ????*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 14:17:14
|
| Well most everytime that I have tried to outsmart SQL Server by doing things like that, I have failed, but not always. Why is reinstalling not an option here? It would probably be faster at this point considering all of the work that you have done already to fix it.Tara |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-01 : 14:55:34
|
OK ... it worked !!!!   KIDS - do not try this at home, and NOWHERE near your prod boxes (or as in our case, your busy-implementing, soon-to-be, prod server).In summary, this is what went on****************************************Problem : TempDB was detached instead of ModifiedEffect : -------------Problem is that when you re-attach tempdb it gets the next available dbid.However, the tempdb dbid is hardcoded as (2).This means we need to get it reset to 2.However, without TempDB its impossible to change data. (Or for that matter to get SQL Server up - without using the -T3608 flag)Solution : ----------1. Stop SQL, copy away ALL original databases (model, msdb, master, tempdb remains)2. Procure a copy of master from another SQL2k db.3. Get this running normally (detach other db's, move files around etc)4. Attach broken master on another name.5. Since box is now working normally (albeit with other server's settings etc) you can edit and change parameters6. Configure 'Allow updates' to 1 and reconfigure with override7. Update master copy's sysdatabases to make tempdb dbid 2 again, update status while you're at it (just in case)8. Stop the server9. Copy away the other server's files thats working to a safe place (JIC)10. Copy the edited datafiles back into the original master location with rename11. Start SQL12. Make a Wish13. SQL Should come up normally.**************************Now the reason we're still kinda ok with this is, tempdb get's re-created (almost) everytime SQL starts, so the fact that we ran for 1 SQL start with a hacked tempdb is ok. Next SQL start will re-create tempdb from model, and then your smiling (so far, anyway).PS - in case your wondering, SQL started after a server re-boot with no issues. And the crowd went WILD !!!!*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-01 : 16:58:24
|
| I can't believe it actually worked! That's so cool. I thought that it might, but I wasn't sure. At least you didn't have to reinstall it.Tara |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-02 : 01:55:00
|
| yeah - and a side product - You're over 1000 !!! well done, and thanks...*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-05-02 : 03:02:18
|
| Wanderer,You should definately report the hard coded tempdb DBID to Microsoft and the hassle it caused. Perhaps they might fix this before Yukon comes out.DavidM"SQL-3 is an abomination.." |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2003-05-02 : 03:40:22
|
| Probably should. We have unfriendly situation where we are an external company providing DBA services, yet the client has outsourced the infrastructure to another company, and hence all calls etc. to MS must go via them (3rd company). We've had to do this, and it ain't fun ... you call them, they pass up several levels', then log call, then MS call them back, it gets passed down several level's, and then they speak to me. Then when anyone wants to make decisions, they don't seem to know who to speak to... *sigh*When I'm a little calmer, I might try informally via SQL user groups ..*#&#* *#&#* *#&#* *#&#* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-02 : 12:28:35
|
| Just send the info to sqlwish@microsoft.com. The e-mail address is for the things that we want to see in the next version, but maybe it'll get into the right hands and this tempdb problem won't happen again to anyone.Tara |
|
|
|
|
PROBLEM - tempdb detached