Sql-server – SQL Server Agent Services not Coming Up

sql serversql-server-2008sql-server-agentwindows-server

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.

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.

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.

Best Answer

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.

Related Solutions

Sql-server – Get failed SQL Server agent job

Here is a proc that will do the trick:

IF (object_id('ShowJobHistory') IS NOT NULL)
BEGIN
  print 'Dropping procedure: ShowJobHistory'
  drop procedure ShowJobHistory
END
print 'Creating procedure: ShowJobHistory'
GO
CREATE PROCEDURE ShowJobHistory
@Days int = 14
as
---------------------------------------------------------------------------------------------------
-- Date Created: September 13, 2010
-- Author:       Bill McEvoy
-- Description:  This procedure produces an easy to read report that details all jobs
--               that have run on this server in the specified number of days.
--               
---------------------------------------------------------------------------------------------------
-- Date Revised: 
-- Author:       
-- Reason:       
---------------------------------------------------------------------------------------------------
set nocount on

---------------------------------------------------------------------
-- Generate report                                                 --
---------------------------------------------------------------------


print ' '
print ' '
print 'RECENT JOB HISTORY'
print '=================='
print ' '

select 'ID'        = convert(char(8), h.instance_id),
       'Run Time'  = convert(char(10),convert(datetime,convert(char(8),h.run_date)),120) + ' ' + left(right('00000' + cast(run_time as varchar(6)),6),2) + ':' + left(right('00000' + cast(run_time as varchar(6)),4),2) + ':' +  right(cast(run_time as varchar(6)),2) + ' ',
       'Duration'  = case when (h.run_duration > 1800) then '>' else ' ' end + left(right(convert(char(19),(dateadd(ss,h.run_duration,'')),20),8),8) + ' ',
       'Status'    = case(run_status)
                       when 0 then '** FAILED ** '
                       when 1 then 'Success '
                       when 2 then 'RETRY '
                       when 3 then 'CANCELLED '
                       when 4 then 'IN PROGRESS '
                       else '??'
                     end,
       'Step'      = convert(char(3),h.step_id),
       'Job Name'  = left(s.name,50),
       'Step Name' = left(h.step_name,35),
       'Message'   = left(h.message,200)
  from msdb.dbo.sysjobhistory h
 right join msdb.dbo.sysjobs s on s.job_id = h.job_id
 where h.run_date >= convert(int,(convert(char(8), (getdate()-@Days),112)))
--   and (h.step_id = 0 or run_status = 0)
 order by h.instance_id desc



go
IF (object_id('ShowJobHistory') IS NOT NULL)
  print 'Procedure created'
ELSE
  print 'Procedure NOT created'
GO

Sql-server – SQL Server Agent

You need access to SQL Server Agent in order to do what you need to do. This is done through msdb permissions: SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.

You can create a job to adhere to your schedule, and then the job will execute whatever task it is that you need done.

As for the performance impact on the server, SQL Server Agent itself is negligable but whatever the job does will definitely be a consideration on when it is run, depending on how expensive the job is. To give a better estimate on server impact, please tell us exactly what you need the job to do.

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

Best Answer

Related Solutions

Sql-server – Get failed SQL Server agent job

Sql-server – SQL Server Agent

Related Question