2

Edit 3 & 4 Added

Although the SQL Server instance is set to run automatically and TCP also is enabled in its Network Configuration, the SQL Server is stopped after each system restart. I have to go into the configuration and start it again manually.

Edit : It's default account is "Network Service"

What is the cause of this, and what solutions are available?

Edit 5 - Repost

The Event Log :

Repost

http://pastebin.com/fdktP3mP

Edit 4 : (Expired Link)

Here is the Last Error Log of today

http://pastebin.com/hLyVHZud

Edit3 : - Part 2/2

Seems that this error doesn't appear on every start cause It's started after my last restart, the time that I wanted to clear and refresh the Logs and probing the error :D

I think in the Event Log as other experts mentioned there are good informative points and I hope we could find the real problem e.g some errors are saying Network Service couldn't login ... but after a minute or so it seems that It logged on !

Kasrak
  • 123
  • 1
  • 7

3 Answers3

1

Is it possible that you have storage that is coming online after the SQL Server starts? For instance, if your master database is on SAN storage that is not being attached to the server until after SQL Server tries to start up. This will cause SQL Server to appear in a Stopped state after a restart, but work when you manually start it. You can create a dependency for the SQL Server service to prevent this.

Do you have a copy of the error log AFTER the start up? The one you provided appears that it is after you manually started it and then had it shutdown.

Brian Knight
  • 1,175
  • 2
  • 8
  • 17
  • master seems to be on local from the sql logs. – tony roth Mar 09 '12 at 15:31
  • Thanks for your response, sorry being late at that time I couldn't give the needed reply, Now everything seems up-to-time and accurate, If any other info is needed I'll provide, the SQL Server is in local as said, thanks in advance – Kasrak Mar 10 '12 at 10:53
  • Looking at your last error log - it says that NetworkService is unable to open a specified database. Your service account should have master set as it's default database. I am wondering if it is set to a different database that has not yet gone through startup recovery some of the time. Ensure the default database is master. – Brian Knight Mar 10 '12 at 19:14
  • @Brian sorry Mr.Brian I didn't see your comment, maybe no notification, anyway yes it's "master", seems that the result happens not every time. any suggestions ? – Kasrak Mar 12 '12 at 20:38
  • In addition seems that there are 2 "Network Service" and "sa" accounts : one is "NT SERVICE\MSSQL$PROSERVER", the other "NT AUTHORITY\NETWORK SERVICE", it wasn't something I decide, maybe that happened during installation unintentionally is it good ? or could it be the cause of some errors ? – Kasrak Mar 12 '12 at 20:47
1

This is a named instance, that I'm guessing was setup to support another piece of software. Check the dependencies of the SQL Server service and see if it's dependent on another service which isn't being started for some reason.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • I'm trying to get it's Event Log, As I remember it wasn't related to Some other Server programs such as sharepoint, in my previous installations of Sql Server I never experienced this, I think in this setup I added some credentials with granted permissions for ease of access, further than that I couldn't remember anything to be dependant, e.g could it be dependant to sa Account ?? shoul agent be run this way ? – Kasrak Mar 09 '12 at 20:46
  • Look in the services applet in Admin tools to see if the MSSQL$PROSERVER service is dependent on any other drivers or services. If the service isn't starting there should be something listed in the Application or System log which says why the service wasn't started or why it failed. If the service isn't starting odds are there won't be anything in the SQL ERRORLOG file. The SQL Instance isn't dependent on the sa account. – mrdenny Mar 10 '12 at 01:47
  • No Sir, It's not dependent to anything, Just as expected sa is dependent to it nothing else, Now I can see a weird change, It's started automatically ! I'll add a piece of info to my first post – Kasrak Mar 10 '12 at 08:07
  • Can you repost the errorlog info? The links aren't working. – mrdenny Mar 12 '12 at 04:07
  • Reposted the Event Log : http://pastebin.com/fdktP3mP – Kasrak Mar 12 '12 at 10:11
  • What's the default database for the login "NT AUTHORITY\NETWORK SERVICE"? – mrdenny Mar 12 '12 at 18:08
  • The Database is "master" – Kasrak Mar 12 '12 at 20:15
  • In addition seems that there are 2 "Network Service" and "sa" accounts : one is "NT SERVICE\MSSQL$PROSERVER", the other "NT AUTHORITY\NETWORK SERVICE", it wasn't something I decide, maybe that happened during installation unintentionally is it good ? or could it be the cause of some errors ? – Kasrak Mar 12 '12 at 20:47
  • Sounds like something is just trying to log into the database and failing. Maybe some service that is configured to connect but the database has been removed. This doesn't have anything to do with your problem of the SQL Server not starting. – mrdenny Mar 14 '12 at 01:56
  • As the SQL Server isn't starting the answer won't be in the SQL Server's ERRORLOG. What's in the system event log after the restart? – mrdenny Mar 14 '12 at 01:57
  • This is an old question here, so after getting a new answer, decided to mark your answer as the answer cause of the volume of participation. – Kasrak Jun 20 '20 at 07:06
0

One possibility is losing logon details and therefore not being able to start the server. Perform the steps below:

  1. Go to SQL Configuration Manager
  2. Open with "Run as administrator"
  3. Make sure start mode is set to automatic
  4. Right click on SQL server instance and set the log on properties like this: enter image description here

and that's it !!

jaume
  • 757
  • 1
  • 6
  • 11
Vanisha Gupta
  • 1
  • Hello, thanks for trying to participate on answering this question, this issue was too old, I changed PCs, OSs and SQLSMS versions. – Kasrak Jun 20 '20 at 07:04