SQL Server Performance Forum – Threads Archive
HELP ME! I have killed my serverHi 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: /*
1) Add -T3608 to SQL Server start-up parameters. Stop and restart SQL Server Service
2) Run Script 1
*/ ————————————-Script 1—————————————————————-
EXEC master..xp_cmdshell ‘move /y "Crogram FilesMicrosoft SQL ServerMSSQLdatamodel.mdf" "Fata FilesSystem Databasesmodel.mdf" ‘
EXEC master..xp_cmdshell ‘move /y "Crogram FilesMicrosoft SQL ServerMSSQLdatamodellog.ldf" "F:Log FilesSystem Databasesmodellog.ldf"’
GO USE master
EXEC master..xp_cmdshell ‘move /y "Crogram FilesMicrosoft SQL ServerMSSQLdatamsdbdata.mdf" "Fata FilesSystem Databasesmsdbdata.mdf" ‘
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——————————————————————
sp_attach_db ‘model’, ‘Fata FilesSystem Databasesmodel.mdf’, ‘F:Log FilesSystem Databasesmodellog.ldf’ USE master
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
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
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
2005-05-12 19:02:53.61 server Using dynamic lock allocation.  Lock Bloc
ks,  Lock Owner Blocks.
2005-05-12 19:02:53.62 server Attempting to initialize Distributed Transactio
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=’‘ />
quote:Originally posted by simondm 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.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!