1

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

Thanks

Update

With all messages enabled , Agent log has suspicious line

[393] 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

Improve this question
5
  • Some internet searches turned up some possibilities. Have you reviewed the Fiber Mode settings for SQL Server. Did you change the account that is running SQL Server Agent?
    – Lee M
    Sep 16, 2021 at 3:19
  • Account wasn't changed as far as I'm aware. I'll double check Fiber mode settings , I believe I went through it already , but definitely check it again.
    – a1ex07
    Sep 16, 2021 at 3:56
  • SQL Agent has its own log, separate to SQL Server's log. Can you get to it?
    – Michael Green
    Sep 17, 2021 at 11:32
  • @MichaelGreen : Yeah, I checked Agent log as well. I should have posted it in the very question from the beginning. Updated.
    – a1ex07
    Sep 20, 2021 at 2:15
  • @a1ex07, did you try to start SQL Agent in a console verbose mode? in CMD: <Path to your SQL Agent exe>\SQLAGENT.EXE" -i instance anme if used -c -v If you do not use named instance you can skip -i. You can also copy a command line from service and add -c -v
    – Mainecoon21
    Jan 23 at 10:29

1 Answer 1

Reset to default
2

Ran query to discover what is growing tempdb, found a spid running from SQLAgent - Generic Refresher and calling EXECUTE msdb.dbo.sp_sqlagent_refresh_job. Once I killed this spid Sql Agent Service came online properly and I am now able to run, view, drop jobs successfully. I dropped all jobs in case this happens again, not certain of root cause, perhaps a bad schedule or job step.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.