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.
![[Company Logo Image]](images/SQLCofee.jpg)
