July 12, 2004 at 1:22 pm
I have exhausted many resources but I am here once again. I installed SQL 2000 SP3 on a brand new 2003 enterprise server. Works great. Went to move log files to a different array then where the data files are located (KB 224071). I moved the user (Northwind and Pubs) DB's first. Then model. Upon removing the -T3608 parameter and hitting the OK button everything went to crap. The logs say: Warning: Problem activating all the Tempdb files. Restart server with -f to correct this. Did that from the cmd prompt. Still can't access my server via EM. Read something referring to -T3609 parameter so I used that to start the server. Now the error logs show that the Master,Northwind,Pubs,MSDB all start but it can't find the database with ID 3. I am lost. Anyone have any clue???? TIA for any response to this.
July 12, 2004 at 3:14 pm
Can you query sysdatabases? select * from master.dbo.sysdatabases?
I bet id3 = tempdb, which means you can't find those mdf/ldf files. Get them and put them in the proper place to start SQL Server, then you can move them according to this technique.
http://www.sqlservercentral.com/columnists/sjones/atemporarymove.asp
July 13, 2004 at 7:31 am
Thank you Steve. I can't query the sysdatabases (tried numerous times) I get erros within QA.
Server: MSG:17, Level:16, State:1, Line:0
[Microsoft] [ODBC SQL Server Drive] [Shared Memory]Sql Server does not exist or access is denied
Server: MSG: 2, Level:16, State:1, Line:0
[Microsoft] [ODBC SQL Server Drive] [Shared Memory] Connection Open (Connect())
Connection Broken
I am logged on as the local admin (this server has not been put into production yet I am just trying to set it up)
I have copied the tempdb files all over my file system. Still doesn't work. Any more ideas??
In the begining when I detached the databases then went to windows explorer to move the log files to the appropiate directoy I would drag them to their destination but they actually copied. They still reside in their original place as well as the new location. Seemed odd. But after I reattached the user databases, all was well (untill the model detach/reattach). Not sure if it is some safety feature from Microsoft.
I am considering starting from scratch again. If so is there a paticular order in which I should move the databases. Maybe master first? then tempdb? I will try using your technique. Thanks for the response.
July 15, 2004 at 9:40 am
You should move Master via the SQL startup parameters. Never try to detach Master, because if somehow it does succeed you will never get it back.
TempDB should only be moved via a ALTER DATABASE statement. If you detach it, it will NOT re-attach using the correct database id. You will end up with a TempDB that cannot be used. You can zap master..sysdatabases if you try hard enough to set TempDB back to ID 3, but this is really messy...
Beyond that, it does not matter which order you move model, msdb, and user databases.
However, if you detach model you MUST re-attach it before you detach anything else. Likewise with msdb. Otherwise these critical databases will get the wrong database id and things will not work properly if at all...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 15, 2004 at 10:03 am
At this point I think you want to uninstall, reinstall SQL. Then you can move the master and other databases to where you need them.
As far as moving:
http://www.sqlservercentral.com/columnists/sjones/moveyourmaster.asp
http://www.sqlservercentral.com/columnists/sjones/movingmsdb.asp
http://www.sqlservercentral.com/columnists/sjones/atemporarymove.asp
July 15, 2004 at 10:14 am
Thanks for the advice, I will start over (thank God it doesn't take long to reinstall SQL) haven't had a chance to fiddle with this the past few days. I have had to take care of a downed e-mail system. I will post the results when I finish. Thanks again!
July 16, 2004 at 6:45 am
Everything went great! I got my database's moved accorrding to Steve's instructions within 15 mins. I made a few typo errors at first so I edited the Registry to reflect the correct file directory name. I have but one more question: Can I safely remove the -T3608 startup parameter now??????? I am hestitant to touch it now that everything is working. Thanks again and I will look forward to reading more of your columns.
July 19, 2004 at 1:28 am
You should remove the -T3608 parameter when you are finished moving the system databases. You will have to restart SQL after removing the parameter, as it affects the way SQL starts.
The -T3608 flag tells SQL to perform restart recovery processing on the Master database only. This is essential if you plan to move Model or MSDB, but it is very unwise to leave this trace flag set for normal running.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply