SQL Server Performance Forum – Threads Archive

HELP ME! I have killed my server

Hi All,
I seem to have killed the server on my machine (not the production server, so it isnt the end of the world) but there are some things i would really like to get back (no, i didnt backup all the DBs on my local machine). Here’s what happened:
I am preparing to move the location of all the data and log files on our live server to different disks, so i am practicing on my maching…I was testing a script to move the msdb and model databases…here it is: /*
Moving MSDB:
1) Add -T3608 to SQL Server start-up parameters. Stop and restart SQL Server Service
2) Run Script 1
*/ ————————————-Script 1—————————————————————-
USE master
GO
sp_detach_db ‘model’
GO
EXEC master..xp_cmdshell ‘move /y "Crogram FilesMicrosoft SQL ServerMSSQLdatamodel.mdf" "Fata FilesSystem Databasesmodel.mdf" ‘
GO
EXEC master..xp_cmdshell ‘move /y "Crogram FilesMicrosoft SQL ServerMSSQLdatamodellog.ldf" "F:Log FilesSystem Databasesmodellog.ldf"’
GO USE master
GO
sp_detach_db ‘msdb’
GO
EXEC master..xp_cmdshell ‘move /y "Crogram FilesMicrosoft SQL ServerMSSQLdatamsdbdata.mdf" "Fata FilesSystem Databasesmsdbdata.mdf" ‘
GO
EXEC master..xp_cmdshell ‘move /y "Crogram FilesMicrosoft SQL ServerMSSQLdatamsdblog.ldf" "F:Log FilesSystem Databasesmsdblog.ldf" ‘
GO ———————————End Script 1—————————————————————- /*
3) Remove trace flag -T3608 from startup parameters. Stop and restart SQL Server Service
4) Run Script 2
*/ ————————————Script 2——————————————————————
USE master
GO
sp_attach_db ‘model’, ‘Fata FilesSystem Databasesmodel.mdf’, ‘F:Log FilesSystem Databasesmodellog.ldf’ USE master
GO
sp_attach_db ‘msdb’, ‘Fata FilesSystem Databasesmsdbdata.mdf’, ‘F:Log FilesSystem Databasesmsdblog.ldf’ ———————————–End Script 2————————————————————– My problem occured somewhere around step 3 (so msdb and model are both detached at the moment)- when i went to remove the trace flag, the service had stopped (i think)…anyway, i cant restart it, and dont know how to go about fixing it! Any help on how to get my server running again would be greatly appreciated!!!! Thanks
Ben

---

You can start SQL server in single user mode. From a command prompt, enter:
sqlservr.exe -c -m http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_startp_4okl.asp
Try again to attach msdb and model databases..read carefully below given links first and findout what you missed previously: – http://support.microsoft.com/default.aspx?scid=kb;en-us;224071 http://www.sqlservercentral.com/columnists/chedgate/movingsystemdatabasesachecklist.asp http://www.databasejournal.com/features/mssql/article.php/3379901
Deepak Kumar –An eye for an eye and everyone shall be blind

---

Before attaching the msdb you need to remove the 3608 trace flag. See the MS article Deepak posted. I have seen problems with detaching the model and msdb at once. I always do them one at a time now. It should be recoverable. Simon

---

Sorry you did do that!! Oops. It is probably because you didn’t reattach the model with the flag still set (yes there different). I would assume it can’t restart now because there is no model for the tempdb to be rebuilt on. Hope this helps.

---

so, any idea how i can restore the model now??? this is the output i got when i tried to start in single user mode like Deepak said:
2005-05-12 19:02:53.42 server Microsoft SQL Server 2000 – 8.00.760 (Intel X8
6)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1) 2005-05-12 19:02:53.43 server Copyright (C) 1988-2002 Microsoft Corporation. 2005-05-12 19:02:53.43 server All rights reserved.
2005-05-12 19:02:53.43 server Server Process ID is 2028.
2005-05-12 19:02:53.43 server Logging SQL Server messages in file ‘Crogram
FilesMicrosoft SQL ServerMSSQLlogERRORLOG’.
2005-05-12 19:02:53.43 server SQL Server is starting at priority class ‘norma
l'(1 CPU detected).
2005-05-12 19:02:53.61 server SQL Server configured for thread mode processin
g.
2005-05-12 19:02:53.61 server Using dynamic lock allocation. [2500] Lock Bloc
ks, [5000] Lock Owner Blocks.
2005-05-12 19:02:53.62 server Attempting to initialize Distributed Transactio
n Coordinator.
2005-05-12 19:02:53.65 server Failed to obtain TransactionDispenserInterface:
Result Code = 0x8004d01b
2005-05-12 19:02:53.67 spid3 Warning ******************
2005-05-12 19:02:53.67 spid3 SQL Server started in single user mode. Updates
allowed to system catalogs.
2005-05-12 19:02:53.67 spid3 CreateRecEvent failed: 5
2005-05-12 19:02:53.67 spid3 Starting up database ‘master’.
2005-05-12 19:02:54.59 spid3 Server name is ‘DEV10’.
2005-05-12 19:02:54.61 server Using ‘SSNETLIB.DLL’ version ‘8.0.766’.
2005-05-12 19:02:54.61 spid7 Starting up database ‘IT Request’.
2005-05-12 19:02:54.62 spid8 Starting up database ‘Test IT Request’.
2005-05-12 19:02:54.62 spid9 Starting up database ‘FNTraining’.
2005-05-12 19:02:54.65 server SQL server listening on 192.168.2.46: 1433.
2005-05-12 19:02:54.65 server SQL server listening on 127.0.0.1: 1433.
2005-05-12 19:02:54.68 spid5 Could not find database ID 3. Database may not
be activated yet or may be in transition. When i then tried to open enterprise manager to remove the flag, i couldnt connect to it…

---

Clost EM.. and open a query analyzer and run your command (which is pending) USE master
GO sp_attach_db ‘model’
, ‘Fata FilesSystem Databasesmodel.mdf’
, ‘F:Log FilesSystem Databasesmodellog.ldf’ GO Deepak Kumar –An eye for an eye and everyone shall be blind

---

I cant connect to the database from QA either (even after running that command from a command line to start in single user mode…, so i cant run the script to attach!

---

its working!!!!!!!! I had to start in single user mode from the command line with the -T3608 parameter too…i could then restore model and msdb! Thankyou for your help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

---

<br />One more Happy DBA, Good to share knowledge [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]<br /><br />Deepak Kumar<br /><br />–An eye for an eye and everyone shall be blind

---

I am a very happy dba indeed! its 9pm, i am still at work, but i know everything will be ready for me when i come back in tomorrow <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />

---

Thanks Simon,
On reading the microsft docs more carefully, i see that it does say to reattach the model with the flag still set…i will need to read things more carefully from now on wont I! I think i might go for the moving them seperately approach from now on too!

---

]]>