September 13, 2004 at 3:21 am
I have 2 instances of SQL Server running on my W2k Server. I can no longer start the default instance, and I get the response
"Could not start the MSSQLSERVER service on Local Computer. The service did not return an error. This could be an internal Windows error or an internal service error."
About the same time I also had a problem where IIS stopped working and the only way I could resolve that was uninstall and reinstall several times. I have had to reinstall SQL Server before because of the same error response, but there is now too much to lose to go that route again. A search on the Internet for this error turned up a post somewhere else where someone indicated changing domains could be related. I'm not sure about this, but I have used this machine on another LAN recently.
I find it confusing that the second instance still runs. This is a development and testing server that I use as standalone most of the time, so the second instance is to simulate a linked server for one project I'm working on.
Can anyone help?
September 13, 2004 at 5:24 am
Have you verified that the user that starts the DEFAULT instance is still active and is spelled correctly?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 13, 2004 at 6:34 am
The user that starts the default instance is LocalSystem. I have changed this to Administrator, which is what the second instance is using too, and back again, but it is still not starting.
September 13, 2004 at 10:43 am
Can you try starting sql Server through command line i.e. using sqlservr application and see if you get any errors ? Below are the list of parametes
sqlservr [-sinstance_name] [-c] [-dmaster_path] [-f]
[-eerror_log_path] [-lmaster_log_path] [-m]
[-n] [-Ttrace#] [-v] [-x] [-g number] [-O] [-y number]
September 13, 2004 at 1:52 pm
I get a response saying it could not find the specified named instance LOCAL, and in the errorlog file I found this:
2004-09-13 11:48:44.38 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2004-09-13 11:48:44.38 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-09-13 11:48:44.38 server All rights reserved.
2004-09-13 11:48:44.38 server Server Process ID is 2068.
2004-09-13 11:48:44.38 server Logging SQL Server messages in file 'f:\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-09-13 11:48:44.42 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-09-13 11:48:44.45 server SQL Server configured for thread mode processing.
2004-09-13 11:48:44.46 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2004-09-13 11:48:44.47 server Attempting to initialize Distributed Transaction Coordinator.
2004-09-13 11:48:45.52 spid3 Starting up database 'master'.
2004-09-13 11:48:45.60 spid3 Error: 9003, Severity: 20, State: 1.
2004-09-13 11:48:45.60 spid3 Cannot recover the master database. Exiting.
It also said that if the installation is corrupt or tampered, uninstall then reinstall to correct the problem. What I want to know is can something be done to keep the info and existing database structures (no back-ups!!!).
September 13, 2004 at 4:13 pm
Well,
If you can't recover Master I think you are going to lose all your user information However, I think you can fix that without too much trouble.
1st copy the .mdb and .ldb of all the databases associated with the DEFAULT instance some place safe (probably don't need to but just to be safe).
2nd shutdown the 2nd instance and copy those database files someplace safe too.
3rd I would then attempt to uninstall the DEFAULT instance and then restart the system and make sure that the 2nd instance wasn't affected.
4th IF the uninstall of the DEFAULT was successful re-install it.
5th Put the DEFAULT databases back where you got them from (If they are no longer there) EXCEPT FOR! master, model, msdb
6th Re-attach databases using the re-attach option for the server (right-click the servername)
7th research sp_adduserlogin (I think it is called) for Auto_fix option. This will need to be run against EVERY database to fix orphans. Hopefully correcting MOST of the user problems...
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 13, 2004 at 5:21 pm
You can also try rebuilding the master with rebuildm utility if you don't want to re-install SQL. This will replace step 3-4 suggested by old hand. You still need to follow the other steps suggested.
September 14, 2004 at 8:47 am
Hello,
I would stop the second instance, copy out all databases as in #1 and #2 by AJ. Then I would rebuild Master by rebuildm by SA24 recommendations. Then I would start SQL Server that will start now and RESTORE Master from Yesterday's backup. Then if you moved all your databases out -> Move them back and restart SQL Server. If you did not move your database out then it would recognize them.
Yelena
Regards,Yelena Varsha
September 14, 2004 at 6:01 pm
I have tried using the rebuildm tool to rebuild the master database on the local instance. I found it opened up a window that says 'configuring server', which stays there showing its progress bar looping over and over. I let it run for several hours before I figured somethings not right. Any pointers from here?
September 14, 2004 at 6:27 pm
I have personally never used the rebuildm tool. I have always done things the "hard" way 🙁 sorry
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 15, 2004 at 9:55 am
Check this KB article. This is a very common cause for rebuild failing.
September 21, 2004 at 9:24 am
Thanks all. Lost my email connection so had to take the plunge. Backed up the data files just in case, then uninstalled and reinstalled SQL Server for the default instance. I was able to reattach my important databases with no info lost.
September 21, 2004 at 9:26 am
Thanks all. Lost my email connection so had to take the plunge. Backed up the data files just in case, then uninstalled and reinstalled SQL Server for the default instance. I was able to reattach my important databases with no info lost.
September 21, 2004 at 10:50 am
Rhett,
You did a good job. Now when your problem is resolved you may want to look back and find out why this happened and how to prevent it. Especially you say it happened before too. You may want to check:
1. Are ALL of your databases including Master and MSDB backed up daily? Don't forget you need to use SQL Server Backup first then Windows one not just Windows Backup. Common misunderstanding of Non-DBAs
2. Are you running Disk Defragmenter? Is there any process that can corrupt Master?
3. Is your Antivirus SW working correctly? I did have a strange case when I restarted one of the servers (it was a while ago, it was SQL Server 6.5) and when SQL Server was coming up another process I don't remember if it was Antivirus or Backup utility grabbed the database file first. But then it was an explicit error message that the database file was used by another application.
Recommendations: if you already had this problem at least 2 times as you say, you have to store a copy of your Master database somewhere in the safe location. Stop SQL Server, copy our MDF and LDF files and restart SQL Server. Do it monthly in addition to daily backups. This way of you do lose Master, you will just replace files, will be able to start SQL Server and then you will restore Master from yesterday's backup.
Regards,Yelena Varsha
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply