Getting error and finding the cause is something which I like. And off late over the blogs I have shared a number of such incidents that have helped me figure out stuffs all by myself. Of course, I do my research and more often than not make things complicated because there are a number of resolutions that I find. I also make it a point to check with a few friends from Microsoft to a possible cause because it is easy to get help than walk the tough line of finding it myself. One fine day I restarted my virtual machine and then I wanted to take a full backup of all databases before formatting the machine. As soon as I hit on execute button, as shown below, I was welcomed with an error message.
Here is the error which I received.
Complete text of the error message is below:
TITLE: Execute Maintenance Plan
——————————
Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)
For help, click: https://www.microsoft.com/en-us/?ProdName=Microsoft+SQL+Server&ProdVer=12.00.2480&EvtSrc=MSSQLServer&EvtID=22022
——————————
BUTTONS:
OK
——————————
Error message is very clear. SQL Server Agent service should be running before we can execute maintenance plan. If we notice the first screen shot, it’s clear that SQL Server Agent was not running. So solution looked very simple, start the SQL Server Agent Service. It was not started by its own because it was set to manual mode.
To avoid such error in the future, I change the “Start Mode” of the service, to automatic by going to the properties of the service.
I also found that sometimes you would get this error even if the SQL Server Agent is running. In those cases, you need to check SQLAgent.out file to check if it’s started completely.
Something SQLAgent.out would show ONLY below message
Waiting for SQL Server to recover databases…
If you are running in that issue, please have a look at https://support.microsoft.com/en-us/kb/2640027 to get a fix for the issue.
Have you even fixed such simple issues? The error messages over the ages have become obvious and very helpful too.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Dear Pinal Sir, hope will be fine. please share some post with respect swap space in data warehousing.
thanks
Hello Pinal sir,
I am new to DBA i have two different job on which stop the SQL Services
and another which first start the Sql Services and then SQL Agent Services using bellow command
To Stop i use: net stop MSSQLSERVER
To Start SQL services i use: net start MSSQLSERVER
To Start SQL Agen Service i use : net start SQLSERVERAGENT
I am getting the same issue discussed above, about the problem
SQL Agent status is Running,
SQL Agent is running from “nt service\mssqlserver” Account,
error that i have got From SQL Server Error Log is
05/26/2016 22:38:53,Server,Unknown,Software Usage Metrics is enabled.
05/26/2016 22:38:47,spid6s,Unknown,Error occured while starting SQL Server Agent. Error code: 22003
05/26/2016 22:38:47,spid6s,Unknown,Error: 50000 Severity: 18 State: 1.
05/26/2016 22:38:47,spid6s,Unknown,Error occured while starting SQL Server Agent. Error code: 22003
05/26/2016 22:38:47,spid6s,Unknown,Error: 50000 Severity: 18 State: 1.
05/26/2016 22:38:47,spid6s,Unknown,StartService() returned error 5 ‘Access is denied.’
05/26/2016 22:38:47,spid6s,Unknown,Error: 22003 Severity: 16 State: 1.
05/26/2016 22:38:42,spid6s,Unknown,Using ‘xpstar.dll’ version ‘2011.110.6020’ to execute extended stored procedure ‘xp_servicecontrol’. This is an informational message only; no user action is required.
05/26/2016 22:38:42,spid6s,Unknown,Attempting to load library ‘xpstar.dll’ into memory. This is an informational message only. No user action is required.
05/26/2016 22:38:42,spid5s,Unknown,Launched startup procedure ‘AutoStart_SQLAgent’.””
Please help with the same
Thanks
Amit Shukla
Who has created AutoStart_SQLAgent ? What exactly you are trying to do?
Helped me so much
Great. Thanks for the comment @Rahil.
You can try this:
———————————————————————
— Start SQL Server Agent when ‘Agent XPs disabled’
EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘Agent XPs’, 1
GO
RECONFIGURE
GO
———————————————————————
–http://zarez.net/?p=2720
———————————————————————
Thank you very much for your post.
It was very helpful.
Thanks @Luisa. I am glad to hear that.
Thanks. done now it’s working. you save my life
Thank you. It worked for me.
hello my problem is the following, I already restarted the sql agent and nothing marks this for me.
SQLServerAgent Error: Request to run job DBA-MASTER \ MSQLA-amex_2014-Ameeeex14-2 (from User sa) refused because the job is already running from a request by User sa.
(Microsoft SQL Server, Error: 22022)