SQL Server Won''t Start

Question

SQL Server Won''t Start

Brian Brant

SSC Eights!

Points: 839
More actions
August 31, 2005 at 9:58 am

#165044

We're having a problem with one of our database servers. The problem is we can't get SQL Server to start. I looked at the services and the MSSQLSERVICE says "starting" but it never actually starts.
I'm thinking this is a Windows (2000) or hardware issue and not specifically SQL Server 2K itself. I say this because we have at least one other application that has a "hung" service. Likewise, after we login to the server, it takes a good 5-10 min for the desktop to load.
Does anyone have an idea on what could be happening or what can be done to fix? I reviewed a couple SQL log files and found some of these items.
From ERRORLOG.1
2005-08-30 10:10:57.32 server LogEvent: Failed to report the current
event. Operating system error = 31(A device attached to the system is not
functioning.).
2005-08-30 10:10:57.57 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
=========================================
From ERRORLOG.2
2005-08-30 07:48:57.80 server LogEvent: Failed to report the current
event. Operating system error = 31(A device attached to the system is not
functioning.).
===================================================
From ERRORLOG.3
2005-08-30 07:34:03.21 server LogEvent: Failed to report the current
event. Operating system error = 31(A device attached to the system is not
functioning.).
2005-08-30 07:34:03.45 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2005-08-30 07:34:03.88 spid3 Starting up database 'master'.
2005-08-30 07:34:05.31 server Using 'SSNETLIB.DLL' version '8.0.311'.
2005-08-30 07:34:05.31 spid5 Starting up database 'model'.
2005-08-30 07:34:05.38 spid3 Server name is 'OurServerName'.
2005-08-30 07:34:05.38 spid8 Starting up database 'msdb'.
2005-08-30 07:34:05.38 spid9 Starting up database 'pubs'.
2005-08-30 07:34:05.38 spid10 Starting up database 'Northwind'.
2005-08-30 07:34:05.38 server Error: 17826, Severity: 18, State: 3
2005-08-30 07:34:05.38 server Could not set up Net-Library 'SSNETLIB'..
2005-08-30 07:34:05.38 server Error: 17059, Severity: 18, State: 0
2005-08-30 07:34:05.38 server Operating system error -1073723998: ..
2005-08-30 07:34:05.40 spid11 Starting up database 'OurDatabase'.
2005-08-30 07:34:05.38 server Unable to load any netlibs.
2005-08-30 07:34:05.46 server SQL Server could not spawn FRunCM thread.
=========================================
From SQLAGENT.OUT
2005-08-30 06:30:20 - ? [130] SQLSERVERAGENT stopping because of Windows
shutdown...
2005-08-30 06:30:23 - ! [298] SQLServer Error: 913, Could not find database
ID 8. Database may not be activated yet or may be in transition. [SQLSTATE
42000] (ConnExecuteCachableOp)
2005-08-30 06:30:23 - ! [298] SQLServer Error: 913, Could not find database
ID 8. Database may not be activated yet or may be in transition. [SQLSTATE
42000] (ConnExecuteCachableOp)
2005-08-30 06:30:27 - ! [359] The local host server is not running
2005-08-30 06:30:28 - ? [098] SQLServerAgent terminated (normally)
Any help or advice is greatly appreciated!
Thanks,
Brian

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

dharper3 SSCrazy Points: 2391 More actions · Answer 1

Hi , yeh I have seen this, and pretty weird. Solution in my case was to reboot server and all has worked fine since. Problem originally occurred after I tried to move MSDB database all was fine prior. Reboot fixed.

Derek

sswords SSCrazy Eights Points: 8207 More actions · Answer 2

The 'A device attached to the system is not functioning' message bothers me. This is usually indicative of a corrupt dll or driver or you may have a disk drive or controller that's failing. I'd advise you to review the server event logs and see if you're getting any indications of where exactly the failure is occuring. This should show up at system startup. Of course if the disk were failing, it could also lead to corrupt dll's, drivers, and data files compounding the problem.

My hovercraft is full of eels.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Run this via Query Analyzer:

SELECT ServerProperty('ProductVersion'),

ServerProperty('ProductLevel')

I'll bet it will return 8.00.311 and SP1.

I suggest you download and install SP3a for SQL Server. That might help solve your problem. (SQL Server is now up to SP4, but I would only apply SP3a at this time). Once you get everything working, then look into applying SP4.

-SQLBill

Brian Brant SSC Eights! Points: 839 More actions · Answer 4

SQLBill -

Thanks for the reply. I did as you said and here's what returns:

8.00.194 RTM

I thought SP3a was applied for SQL Server. However, a rather unreliable vendor did this for us and it's possible it may not be installed correctly. Is there any way I can verify?

I did an @@version and the following was returned:

Thanks,

Brian

Brian Brant SSC Eights! Points: 839 More actions · Answer 5

sswords -

Our server group has been reviewing the logs and they're not seeing anything out of the ordinary.

They tried to kill the MSSQLSERVER process but couldn't until DTC was disabled. Not sure what this has to do with it but as long as DTC is disabled, they can kill and successfully restart the service. Certainly not a perm. fix but it is one thing that was discovered so far.

We did a dbcc check and the database looks good from that aspect (i.e. everything is there an doesn't show signs of corruption). Just a bit frustrating because nothing is really jumping out indicating what the issue is such as errors in the database and/or server logs or event viewer.

Thanks for your post.

Brian

SQLBill SSC Guru Points: 51440 More actions · Answer 6

194 RTM means there aren't ANY service packs applied.

Download SP3a from Microsoft's website, read the install directions and apply it. You will have to give the SA account a strong password if it doesn't have one already.

-SQLBill

Brian Brant SSC Eights! Points: 839 More actions · Answer 7

I applied sp3a. Unfortunately the same problem still exists - after logging on to the server the desktop is slow to load and when it does SS2K is still hung and the MSSQLSERVER service cannot be stopped and won't start.

One thing we did find is that if the sqlservr.exe and sqlmangr.exe processes are killed and you wait about 5 minutes it is then possible to manually get the MSSQLSERVER service (and the SQLSERVERAGENT service for that matter) to start. So we're not totally out of the water, just not running as we should.

If anyone has experienced a situation like this, any advices or suggestions on what could be the problem are greatly appreciated.

Thanks,

Brian

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Do you have a strong password on the SA account? (Installing SP3a should have required this). I suggest running an updated antivirus on your system; since you had an unprotected (no service pack) SQL Server install you were 'open' to the SLAMMER worm and may have gotten infected. Not definitely, but it is a possibility.

-SQLBill

Brian Brant SSC Eights! Points: 839 More actions · Answer 9

Yes. Definately had a stong password for the SA account prior to the issue we're experiencing. We have anti-virus software with the latest definitions installed on this server (this was installed & maintained when the server before the server went live a couple of years ago).

The only software we installed after the issue surfaced was anti-spyware although running it has turned up nothing. From a virus/spyware standpoint, we should be pretty clean which is good but it makes the troubleshooting even more frustrating.

Thanks,

Brian

SQLBill SSC Guru Points: 51440 More actions · Answer 10

Now that you know your system is clean....set your antivirus to ignore the extensions .mdf, .ndf, and .ldf. I have found that antivirus programs can cause errors with SQL Server by checking those files while SQL Server is running. We just run AV against the rest of the files and whenever we stop the services, then we check the SQL Server files.

-SQLBill

Brian Brant SSC Eights! Points: 839 More actions · Answer 11

I did try one experiment today. (Nothing like "experimenting" on a production box!!! ) Anyhow, what I did was changed the MSSQLSERVER and SQLSERVERAGENT services to manual. I then rebooted the server and the problem went away. The desktop loaded in a timely fashion and SQL Server wasn't hung since the processes didn't start.

It's by no means the ultimate fix but at hopefully that test indicated that SQL is at fault. I'm just wondering if doing an uninstall/reinstall of SQL Server might make a difference. Not the ideal thing to do but seems less risky than restoring the entire server (which our vendor recommended) from our last good backup.

Still not sure what would suddenly cause the SQL services to hang when set to automatic for startup.

Brian

SQLBill SSC Guru Points: 51440 More actions · Answer 12

oooooooo....check to see that the SQLServerAgent service is set to be dependent on MSSQLServer service. If the agent service tries to start first, that will cause problems.

Or set MSSQLServer service to automatic and SQLServerAgent service to manual. That's how I have mine set up.

-SQLBill

dharper3 SSCrazy Points: 2391 More actions · Answer 13

Brian, as per my suggestion early on (reboot server) this appears to have worked, can you advise whther this was the first reboot since encountering your problem? As I said before seems like odd way to solve the issue but this worked for me on a prior occasion. I think that setting the services to manual prior probably had no bearing on the solution (unless the services are setup as per sqlbills comments).

Derek

Brian Brant SSC Eights! Points: 839 More actions · Answer 14

SQLBill -

Checked the dependencies and SQLSeverAgent is dependant on MSSQLServer. I might give it a try with MSSQLServer set to auto and Agent set to manual just to see what happens.

Brian