• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2615
  • Last Modified:

SQL Server service unable to restart after stop unexpectedly

The service account has permission "login as serive account" on the server, and it is able to start, and stop the sql server service.

However, we had an error recently, the service got terminated unexpectedly, and unable to restart due to access denied.  Here is the error log.

[012] The MSSQLSERVER service terminated unexpectedly
[139] AutoRestart: Attempting to restart the MSSQLSERVER service (attempt #1)...
[368] Auto Restart: Unable to restart the MSSQLSERVER service (reason: Access is denied)
[139] AutoRestart: Attempting to restart the MSSQLSERVER service (attempt #2)...
[368] Auto Restart: Unable to restart the MSSQLSERVER service (reason: Access is denied)
[139] AutoRestart: Attempting to restart the MSSQLSERVER service (attempt #3)...
[368] Auto Restart: Unable to restart the MSSQLSERVER service (reason: Access is denied)
[140] AutoRestart: The MSSQLSERVER service could not be restarted after 3 attempts
[360] SQLServerAgent initiating shutdown following MSSQLSERVER shutdown


I tried to search on the internet, but seems a lot of people have this problem, but there is no solution available.  Can you help please, thanks.


0
soIT
Asked:
soIT
  • 3
  • 3
1 Solution
 
cmorffewCommented:
I would check your system logs and see if there is more information in there.
0
 
soITAuthor Commented:
there are hundreds of error around that time, I will post some samples here:

First error:
Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    9001
Date:        05/01/2010
Time:        21:00:02
User:        Domain\sqlaccount
Computer:    SQLSERVER
Description:
The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.


These two errors repeated many times:
Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    9001
Date:        05/01/2010
Time:        21:00:02
User:        Domain\sqlaccount
Computer:    SQLSERVER
Description:
The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    3314
Date:        05/01/2010
Time:        21:00:02
User:        Domain\sqlaccount
Computer:    SQLSERVER
Description:
During undoing of a logged operation in database 'msdb', an error occurred at log record ID (291891:56:5). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

Then I get:
Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    3449
Date:        05/01/2010
Time:        21:00:02
User:        N/A
Computer:    SQLSERVER
Description:
SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
Then:
Event Type:    Information
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    19032
Date:        05/01/2010
Time:        21:00:02
User:        N/A
Computer:    SQLSERVER
Description:
SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Then:
Event Type:    Error
Event Source:    DatabaseMail
Event Category:    None
Event ID:    0
Date:        05/01/2010
Time:        21:00:03
User:        N/A
Computer:    SQLSERVER
Description:
Database Engine Instance=SQLSERVER;Mail PID=8292;Error Message:

1) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 20
LineNumber: 0
Number: 109
Procedure: NULL
Server: SQLSERVER
State: 0
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
HelpLink: NULL

StackTrace Information
===================
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.PostResponse(Response r, Guid convHandle)
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.PostResponseIfNeeded(MailInfo mi, Response r)
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
Then:
Event Type:    Error
Event Source:    DatabaseMail
Event Category:    None
Event ID:    0
Date:        05/01/2010
Time:        21:00:03
User:        N/A
Computer:    SQLSERVER
Description:
Database Engine Instance=SQLSERVER;Mail PID=8292;Error Message:The connection is not open.

Then:
Event Type:    Error
Event Source:    MSSQLSERVER
Event Category:    (2)
Event ID:    19019
Date:        05/01/2010
Time:        21:00:06
User:        N/A
Computer:    SQLSERVER
Description:
The MSSQLSERVER service terminated unexpectedly.
Then:
Event Type:    Error
Event Source:    Report Server Windows Service (MSSQLSERVER)
Event Category:    Management
Event ID:    107
Date:        05/01/2010
Time:        21:00:26
User:        N/A
Computer:    SQLSERVER
Description:
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

10 Min later, we did a manual start of the service, everything went back to normal, no more errors.

That's a lot of stuff I just posted, hopefully one of them will be useful to you.  Thanks.










0
 
cmorffewCommented:
for some reason the msdb log file (.trn) was unable to be opened.  Do you have the .mdf (data) and .trn (trasnsaction Log) files on different hard drives?

if you do i would check and make sure the drive with the log file on it is not full or failing.

I would also check the msdb database to make sure there are no errors with it.

The rest of the errors are related the to the server trying to recover the database.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
cmorffewCommented:
oops the .trn file should be the .ldf file extension.
0
 
soITAuthor Commented:
Thanks cmorffew.
We will investigate a bit, get back to you next week.
 
0
 
soITAuthor Commented:
the problem did not occur again.  We are not exactly sure what went wrong, but as you suggested, we went though all the logs to check if there was anything unusual.  Thanks very much for your time and effort.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now