SQL Server sometimes behaves very strangely for no apparent reason. Few days back I was playing with our staging server and I had to stop SQL Server service for some testing purposes. When I started it back, I saw that SQL Server Agent was disabled and next to it a message showing that “Agent XPs disabled”
From my experience it generally means that SQL Server Agent service is not running. But looking at SQL Server Configuration Manager I found that service was just OK. But just to try, I restarted SQL Server Agent but I was still getting same message and disabled SQL Agent !!
After some googling I found that it is rather very common issue with SQL Server Agent !! Apparently sometimes for no reason, Agent XPs gets disabled in SQL Server environment. When I ran,
EXEC sp_configure 'Agent XPs'
It showed me that runtime value was 0 !! I was sure that other than me nobody changed anything to that machine and I was also sure that I didn’t changed this property. Then how the hell it changed to 0 ?? I was not able to determine any particular reason for this behavior.
Once I changed runtime value of “Agent XPs” to 1, that message was gone by itself.
EXEC sp_configure 'Agent XPs',1 RECONFIGURE
This is really strange bug and I guess there is no solution for it. But SQL Agent started to work again which solves my problem (at least for now )
It’s Just A Thought …