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.
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
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)