[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 Change Pending State
 

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
Tips
Troubleshooting
Tuning

Change Pending State.


Applies to: Microsoft SQL Server 2008 R2 Enterprise Edition.
 

Problem Description.
 

I tried to apply SP1 to a SQL Server 2008 R2 RTM instance used for testing purposes, but at the end SQL Server Setup program displayed the following error: The NT service 'SQLSERVERAGENT' could not be started.

When I examined the Summary.txt log file I found the following information:

Overall summary:
Final result: The patch installer has failed to update the following instance:. To determine the reason for failure, review the log files.
Exit code (Decimal): -2068709375
Exit facility code: 1202
Exit error code: 1
Exit message: The NT service 'SQLSERVERAGENT' could not be started.
Start time: 2012-10-15 11:38:10
End time: 2012-10-15 12:20:02
Requested action: Patch



Exception summary:
The following is an exception stack listing the exceptions in outermost to innermost order
Inner exceptions are being indented

Exception type: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInvalidOperationException
Message:
The NT service 'SQLSERVERAGENT' could not be started.
Data:
DisableWatson = true
Stack:
at Microsoft.SqlServer.Configuration.SetupExtension.RunPatchAllInstanceAction.RestoreServiceStates()
at Microsoft.SqlServer.Configuration.SetupExtension.RunPatchAllInstanceAction.ExecuteAction(String actionId)
at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)


Then I opened SQL Server Configuration Manager and I found SQL Server service on "Change pending" state.







I tried to start the instance in single-user mode, and restarted the computer and now the SQL Server service showed "Stopped" as state. I tried to start the service manually but I received the error "The service did not respond to the start or control request in a timely fashion".


I decided to examine SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG) to see what is stopping SQL Server to start and found the following:

2012-10-15 12:53:11.71 spid7s Starting up database 'mssqlsystemresource'.
2012-10-15 12:53:11.72 spid7s Error: 17204, Severity: 16, State: 1.
2012-10-15 12:53:11.72 spid7s FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
2012-10-15 12:53:11.72 spid7s Error: 5120, Severity: 16, State: 101.
2012-10-15 12:53:11.72 spid7s Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
2012-10-15 12:53:11.74 spid7s Error: 17207, Severity: 16, State: 1.
2012-10-15 12:53:11.74 spid7s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf'. Diagnose and correct the operating system error, and retry the operation.
2012-10-15 12:53:11.74 spid7s File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf" may be incorrect.
2012-10-15 12:53:11.74 spid7s Error: 945, Severity: 14, State: 2.
2012-10-15 12:53:11.74 spid7s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.


The database mssqlsystemresource was not found on C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\ .

 



Solution.


We then copy the resource database from another SQL Server instance on production to the Binn folder and we were able to start the SQL Server service using SQL Server Configuration Manager.



 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.