SQLServerAgent is not currently running so it cannot be notified of this action.

Chris Skorlinski
Microsoft SQL Server Escalation Services

Have you seen errors like these below when attempting to run SQL Replication Wizard or start a SQL Agent job? 

TITLE: Microsoft SQL Server Management Studio
------------------------------
SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

SQL Server blocked access to procedure 'dbo.sp_get_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

Check the SQLAGENT.out file in the SQL Server \LOG folder for more details. 

SQLAGENT.OUT
------------------------------
2010-05-30 20:40:27 - ? [393] Waiting for SQL Server to recover databases...
2010-05-30 20:48:34 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...
2010-05-30 20:48:35 - ? [098] SQLServerAgent terminated (normally)

The first message indicates SQL Agent Services is waiting for the SQL Engine to recover a database.  Also looks like the user got a bit excited and restarted SQL Server Agent in attempt to clear up the problem, except there isn’t any with SQL Agent.  SQL Server Management Studio confirms the “In Recovery” for one database.

image

This same status also shows in the SQL Server errorlog.

2010-05-30 21:00:44.33 spid24s     Recovery of database 'AdventureWorksDW2008Big' (12) is 17% complete (approximately 5605 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.

Yeah, may be a problem with this database as it shows recovery estimated at 93 minutes but don’t blame SQL Server Agent.  It has to wait for the database to recover before it can start launching SQL Agent Jobs.

For this customer it was more important to get SQL Server Agent running then to wait for this particular database to recover.  Therefore we:

  1. Stopped SQL Server
  2. Renamed the database transaction log from .ldf to .xyz
  3. Restart SQL Server

The 'AdventureWorksDW2008Big' database now came up as suspect.  No we still can’t access that database, but SQL Agent is up and running for all other databases.  We’ll work on the problem database at a later time.