February 8, 2007 at 3:27 am
Hi,
This may sound like a very silly and simple question, but if you are logging on as a local administrator and the SQL service runs of this account, have you changed your password at all.
I know it sounds obvious but best to check.
Cheers
February 8, 2007 at 3:29 am
That's a difficult one to answer, Gail. I have been using Windows Authentication, with my first name as User (with Administrator rights) and a blank password.
Not what are you logging in as. What is the SQL Service account. Go to administrative tools. Open services. Go down to SQL Server. Double click the service. Select the log on tab and see who what the SQL Service account is.
Who you are logging in as is a little irrelevent if the service won't start.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2007 at 3:36 am
OK, here it is:
Local System Account, with "Allow services to interact with desktop" ticked.
Hardware Profile = "Profile 1"
Service = "Enabled"
Lester Vincent
Sydney
February 8, 2007 at 5:50 am
Very odd. Local system should always be able to log on. does agent have the same service account?
Do you know a local admin account for the machine? (not a domain account) If so, try using that as the service account.
Anything relevant in the windows event log?
Do you have any domain experts around there? If so, ask them about the domain authent problem you mentioned.
Otherwise, i'm out of ideas.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2007 at 6:24 am
Couldn't see if this was mentioned, but what about the SQL Error log? Does it even start one whenever you try to start SQL? The error log is in text file named ERRORLOG in the LOG directory where you installed SQL.
February 8, 2007 at 7:04 am
Hi Lester. Where are you at with this?
BOL 2005 did not screw up SQL 2000. .NET didn't do it either, and don't try SP4 until your server is UP. It's a waste of time.
If your SQL service is set to start as LOCAL SYSTEM then the authentication part of the equation is 99.9% eliminated.
There are messages in the event logs when you try to start SQL server. Post the messages.
February 8, 2007 at 9:13 am
When I suggested going to Services and restarting them, I did not mean the SQL Server service tool.
Go to Start>Control Panel>Administrative Tools>Services
or right click on the My Computer icon, select Manage, Expand Services.
Find the MSSQLServer and SQLServerAgent services. Are both started? What login do they use? Double click on MSSQLServer. Go to the Dependency Tab. Is the service dependent upon anything?
Also.............you should NEVER, NEVER, NEVER use a blank password on any account, much less one that has admin privileges.
-SQLBill
February 8, 2007 at 3:06 pm
What does the SQL Server errorlog have to say ? If the service is not startingis should only be 5-8 lines so you post the full text. The default location is:
C:\Program Files\Microsoft SQL Server\MSSQL\LOG
The file(s) are errorlog, errorliog.1, etc.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 8, 2007 at 7:30 pm
Hi Jeff, Rudy, SQLBill.
Thanks for your help. I am still in the same situation with this.
As promised, I am posting details from the PC's ERRORLOG files as follows: -
Contents of the most recent ErrorLog file: -
-----------------------------------------
2007-02-09 09:16:37.71 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.1 (Build 2600: Service Pack 2)
2007-02-09 09:16:37.71 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-02-09 09:16:37.71 server All rights reserved.
2007-02-09 09:16:37.71 server Server Process ID is 4040.
2007-02-09 09:16:37.71 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2007-02-09 09:16:37.74 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2007-02-09 09:16:37.89 server SQL Server configured for thread mode processing.
2007-02-09 09:16:37.89 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2007-02-09 09:16:37.90 server Attempting to initialize Distributed Transaction Coordinator.
2007-02-09 09:16:38.01 server Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2007-02-09 09:16:38.03 spid3 Starting up database 'master'.
2007-02-09 09:16:38.14 spid3 Error: 9003, Severity: 20, State: 1.
2007-02-09 09:16:38.14 spid3 Cannot recover the master database. Exiting.
Contents of ERRORLOG.1: -
-----------------------
2007-02-09 08:27:50.43 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.1 (Build 2600: Service Pack 2)
2007-02-09 08:27:50.43 server Copyright (C) 1988-2002 Microsoft Corporation.
2007-02-09 08:27:50.43 server All rights reserved.
2007-02-09 08:27:50.43 server Server Process ID is 1352.
2007-02-09 08:27:50.45 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2007-02-09 08:27:50.48 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2007-02-09 08:27:50.90 server SQL Server configured for thread mode processing.
2007-02-09 08:27:50.95 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2007-02-09 08:27:51.04 server Attempting to initialize Distributed Transaction Coordinator.
2007-02-09 08:27:51.56 server Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2007-02-09 08:27:51.65 spid3 Starting up database 'master'.
2007-02-09 08:27:52.14 spid3 Error: 9003, Severity: 20, State: 1.
2007-02-09 08:27:52.15 spid3 Cannot recover the master database. Exiting.
The others all have the same info.
I hope you can make something of this.
The event viewer contains numerous attempts by the system to start up SQL Server. Here's an example sequence:
"SQL Server is starting at priority class 'normal'(1 CPU detected)."
"SQL Server configured for thread mode processing"
"Using dynamic lock allocation. [2500] Lock Blocks.[5000] Lock Owner Blocks."
"The Security Center Service has been stopped. It was prevented from running by a software group policy."
Then,
"Error. 9003, Severity: 20, State: 1"
"Cannot recover the master database. Exiting"
Then
"Automatic certificate enrollment for local system faled to contact the active directory (0x8007054b). The specified domain either does not exist or could not be contacted. Enrolment will not be performed."
When I started SQL Server originally, I simply used Windows Authentication and it has been no problem till now.
Several months ago, when visiting my client, I spoke to their Systems support consultant who is also their Windows SBS administrator, and who seems well versed in SQL DB Administration.
I asked him to set up my PC (a) so I could access the company's domain when I am on-site, so the unfinished SQL-S database could be installed and modified from my PC. He did so, and this has worked when I am on-site.
Until the current failure of access, I have been able open SQL Server at any time when away from that site.
This is reflected in the fact that My PC has 3 logins with Admin rights:
Lester
QBS (which operates with a password) when I am connected to thwe client's network domain), and
Administrator
I should be able to get into SQL Server, using Windows Authentication, with either "Lester" or "Administrator" logins.
Feel like I'm playing Snakes and Ladders - still throwing the dce to get a six to start.
Thanks again for your time, guys. I'm in awe of the generosity of members of this forum.
Lester Vincent
Sydney
February 8, 2007 at 9:47 pm
Hi people.
JUst a quick note to let you know that I solved the problem by uninstalling SQL Server and re-installing it. Have "attached" the subject database and all is sweet again, and with SP 4.0 installed as well. I guess it helped that it was just the one PC ("local") installation ...
Many thanks to everyone who generously gave some help. It's a hard way to learn, but I still do not know what could have caused the problem.
Regards to all,
Lester Vincent
February 8, 2007 at 9:57 pm
Cannot recover the master database. Exiting
There's your problem. Wiith the master db corrupt, there's no way SQL will start. May have been disk corruption. Might be worth while checking the disk for integrity.
A reinstall will fix things as it removes the master and recreates it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2007 at 10:13 pm
That makes sense gail. I saw it there in the event log several times but was focused on the login aspect.
I recently could not get into XP and had to do a defrag in safe mode to open it up.
My reading of the events log suggested some early signals that all was not right. But that is easier to see in retrospect. And I probably could not have done anything about it anyway.
Thanks for the hours you have put in on this! You didn't let it go, did you?
Cheers,
Lester
February 9, 2007 at 9:23 am
As said, the problem was with your master database. Here's a few reasons that might have occurred:
1. The hard drive was removed without SQL Server being properly shutdown.
2. The files (.mdf/.ldf) were moved incorrectly.
3. The defragging of the drive caused the corruption.
4. something else happened that you aren't aware of.
I have heard that the best thing to do if you want to defrag your drives is to put SQL Server offline.
-SQLBill
February 9, 2007 at 4:19 pm
Thanks Bill.
Referring to your points,
1. and 2. did not happen. I did not, and never do remove the HDD until all the "lights go out". The .mdf and .ldf files have never been moved.
That leaves the defrag as the most likely possibility. It was only performed - in Windows "safe" mode - because XP Pro could not open. Since the defrag, there have been no probs with XP Pro opening. The only problem was with SQL Server opening. I guess if defrag becomes necessary again, in the same circumstances, I should open in safe mode, then copy all files in the MSSQL\BINN and MSSQL\DATA folders to another HDD before running defrag.
Is that what your last sentence is suggesting?
Lester Vincent
Sydney
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply