I wonder what can cause SQLServerAgent to stay in "starting" stage after restart. The service restarted successfully, but I can't get details of any job, or start/stop/delete with msdb procedures. It gives the same error
Msg 14258, Level 16, State 1, Procedure msdb.dbo.sp_is_sqlagent_starting, Line 9 [Batch Start Line 200] Cannot perform this operation while SQLServerAgent is starting. Try again later.
I tried different ways of restarting the server , with SQLServer Management studio, SQLServer Configuration Manager, even manually with NET STOP/NET START.
"SQLServerAgent service successfully started." No errors in Agent log file.
I'm using SQLServer 2017
With all messages enabled , Agent log has suspicious line
 Waiting for SQL Server to recover database 'msdb'...
No errors though, but maybe I didn't wait long enough - I stopped the agent after an hour. Just in case , I verified msdb state in [sys].[databases], state is 0, state description ONLINE .
While agent was in this "starting mode", I kept monitoring queries, and found the one that kept running again and again on msdb database(posting just the very beginning, it seems to be a system function)
CREATE FUNCTION sys.fn_sqlagent_jobsteps(@job_id UNIQUEIDENTIFIER NULL, @step_id INT NULL ) RETURNS @agent_jobsteps TABLE (job_id UNIQUEIDENTIFIER NOT NULL, step_id INT NOT NULL, .....
After I killed the session with
kill, the agent finally started and picked up scheduled jobs. That's definitely not a solution, just a temporary hack that allowed me to keep the system up and running.
I already removed all non-necessary jobs ( some articles suggested that too many scheduled jobs might cause agent to hang) .
Agent is running under virtual account NT Service\SQLSERVERAGENT