3

After the Windows Server 2008R2 was restarted after some planned downtime, the SQL Server Agent Service is not getting started. There are multiple SQL Server named instances and Agent Services of non are getting started. SQL Server Version: SQL Server 2008 SP3

Please note the following errors: 1. When starting from Configuration Manager, following error is generated in Event Viewer.

The SQL Server Agent service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion.

A timeout was reached (30000 milliseconds) while waiting for the SQL Server Agent service to connect.

  1. When starting from SSMS (by right click Agent Service > START)

See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text ************** System.NullReferenceException: Object reference not set to an instance of an object.

Server stack trace: at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.FindObjectExplorerFrame() at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.GetObjectExplorer(Boolean activateWindow) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ShowError(Exception e) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start() at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase) at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ServiceActionHandler.EndInvoke(IAsyncResult result) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ServiceControlProgress.ActionCompletedHandler(IAsyncResult result)

************** Loaded Assemblies ************** mscorlib Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.8762 (QFE.050727-8700) CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll ---------------------------------------- ControlService Assembly Version: 10.0.0.0 Win32 Version: 2007.0100.1600.022 ((SQL_PreRelease).080709-1414 ) CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20SQL%20Server/100/Tools/Binn/VSShell/Common7/IDE/ControlService.exe ---------------------------------------- System Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.8770 (QFE.050727-8700) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll ---------------------------------------- Microsoft.NetEnterpriseServers.ExceptionMessageBox Assembly Version: 10.0.0.0 Win32 Version: 10.50.1600.1 ((KJ_RTM).100402-1539 ) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.NetEnterpriseServers.ExceptionMessageBox/10.0.0.0__89845dcd8080cc91/Microsoft.NetEnterpriseServers.ExceptionMessageBox.dll ---------------------------------------- System.Windows.Forms Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.5491 (Win7SP1GDR.050727-5400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll ---------------------------------------- System.Drawing Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.5495 (Win7SP1GDR.050727-5400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll ---------------------------------------- Microsoft.SqlServer.SString Assembly Version: 10.0.0.0 Win32 Version: 10.50.1600.1 ((KJ_RTM).100402-1539 ) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.SString/10.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.SString.dll ---------------------------------------- System.Xml Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.8773 (QFE.050727-8700) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll ---------------------------------------- ObjectExplorer Assembly Version: 10.0.0.0 Win32 Version: 10.0.5500.0 ((Katmai_PCU_Main).110921-2358 ) CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20SQL%20Server/100/Tools/Binn/VSShell/Common7/IDE/ObjectExplorer.DLL ---------------------------------------- System.Management Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.8766 (QFE.050727-8700) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll ---------------------------------------- System.Data Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.8762 (QFE.050727-8700) CodeBase: file:///C:/Windows/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll ---------------------------------------- System.Web Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.8762 (QFE.050727-8700) CodeBase: file:///C:/Windows/assembly/GAC_32/System.Web/2.0.0.0__b03f5f7f11d50a3a/System.Web.dll ---------------------------------------- System.Configuration Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.5483 (Win7SP1GDR.050727-5400) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll ---------------------------------------- Microsoft.SqlServer.SqlTools.VSIntegration Assembly Version: 10.0.0.0 Win32 Version: 10.0.5500.0 ((Katmai_PCU_Main).110921-2358 ) CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20SQL%20Server/100/Tools/Binn/VSShell/Common7/IDE/Microsoft.SqlServer.SqlTools.VSIntegration.DLL ---------------------------------------- Microsoft.VisualStudio.Shell.Interop Assembly Version: 7.1.40304.0 Win32 Version: 7.10.6071 CodeBase: file:///C:/Windows/assembly/GAC/Microsoft.VisualStudio.Shell.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.Interop.dll ---------------------------------------- Microsoft.VisualStudio.OLE.Interop Assembly Version: 7.1.40304.0 Win32 Version: 7.10.6070 CodeBase: file:///C:/Windows/assembly/GAC/Microsoft.VisualStudio.OLE.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.OLE.Interop.dll ---------------------------------------- SqlWorkbench.Interfaces Assembly Version: 10.0.0.0 Win32 Version: 10.0.5500.0 ((Katmai_PCU_Main).110921-2358 ) CodeBase: file:///C:/Program%20Files%20(x86)/Microsoft%20SQL%20Server/100/Tools/Binn/VSShell/Common7/IDE/SqlWorkbench.Interfaces.DLL ---------------------------------------- Microsoft.VisualStudio.Shell Assembly Version: 2.0.0.0 Win32 Version: 2.0.50727.1826 (QFE.050727-1800) CodeBase: file:///C:/Windows/assembly/GAC_MSIL/Microsoft.VisualStudio.Shell/2.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.dll ---------------------------------------- Microsoft.VisualStudio.Shell.Interop.8.0 Assembly Version: 8.0.0.0 Win32 Version: 8.0.50727.1826 (QFE.050727-1800) CodeBase: file:///C:/Windows/assembly/GAC/Microsoft.VisualStudio.Shell.Interop.8.0/8.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.Interop.8.0.dll ---------------------------------------- Microsoft.SqlServer.Management.SDK.SqlStudio Assembly Version: 10.0.0.0 Win32 Version: 10.0.1600.22 ((SQL_PreRelease).080709-1414 ) CodeBase:

file:///C:/Program%20Files%20(x86)/Microsoft%20SQL%20Server/100/Tools/Binn/VSShell/Common7/IDE/Microsoft.SqlServer.Management.SDK.SqlStudio.DLL

************** JIT Debugging ************** To enable just-in-time (JIT) debugging, the .config file for this application or computer (machine.config) must have the jitDebugging value set in the system.windows.forms section. The application must also be compiled with debugging enabled.

For example:

When JIT debugging is enabled, any unhandled exception will be sent to the JIT debugger registered on the computer rather than be handled by this dialog box.

  1. When checking SQLAGENT.out file:

SQLServer Error: 65535, SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. [SQLSTATE 08001]

[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

[298] SQLServer Error: 65535, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]

[382] Logon to server 'xxx' failed (ConnUpdateStartExecutionDate)

Workarounds already tried: 1. Changing the logon accounts to other domain users account and localsystem, networkservice etc, still same error. 2. Tried this solution from Microsoft Support https://support.microsoft.com/en-in/help/922918/a-service-does-not-start-and-events-7000-and-7011-are-logged-in-window, but still same error.

Please suggest how to resolve this issue.

  • It is already added in the local admin group (the domain user account in this case) and also given this account full control access the SQL Server location folders as well. And also, tried with NetworkService and LocalSystem logon accounts as well. – Shaurya S Jul 3 '18 at 17:02
  • I checked that and it is showing the correct server name, which is the named instance SQL Server name. Also tried starting after keeping it blank, but same error. – Shaurya S Jul 3 '18 at 17:05
  • @Shanky Yes same error is coming [SQLSTATE 08001]. I also suspect there is some issue with the registry, but I dont think someone played with the registry settings. Can you please tell what info this DMV will give and how to use that information to further resolve the issue? Thanks! – Shaurya S Jul 3 '18 at 17:08
  • All the other services including SQL Server, SQL Server Browser, Reporting Services etc are starting up and running just fine. Issue persists only and only with the SQL Server Agent Services. – Shaurya S Jul 3 '18 at 17:10
1

This was solved on my side by altering few steps in the registry, as described by Pinal Dave in SQL SERVER – SQL Server Agent Not Starting – Failed to Initialize SQL Agent Log (Reason: Access is Denied):

It looks like the SQL Agent Log file name is missing from the string and we had to edit that to fix the issue. From my experience, I know that this information is stored in,

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\SQLServerAgent\ErrorLogFile

After we added the log file name we were able to start SQL Agent successfully.

0

If this issue happens only during startup and service starts fine while starting manually then we can try to increase the timeout from 30 seconds to 60 seconds. Here are the steps to change the timeout value. SQL SERVER Timeout error

Did you set the service to start as Automatic (delayed)? If not check it once sometimes dependent components (disk drives, network shares etc..) take longer time to start up.

If this is not working, this issue happens because the SQL Server lost the logon id credentials or used an account without rights to start services. If you miss login credentials need a reboot of your windows server.

SQL Server needs an account with rights to start services during operating system start up, for starting your services automatically.

To solve this issue you need to go Administrative Tools -> Services -> SQL Server(MSSQLSERVER or the name of your instances) -> Right Click -> Properties -> Move to Log On -> Set the Log On option using a Local System Account

If you have chosen this option, hit on Browse Button and find the Local Service Account or Network service Account there > Click in Apply.

Restart your computer and you will able to start your SQL Server services.

  • Have already tried all these, but no success. – Shaurya S Jul 4 '18 at 2:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.