sqlorcl

Just another WordPress.com site

Monthly Archives: February 2013

SQL server agent Xps disabled

SQL server agent- agent Xps disabled

There is some interesting observation regarding this error. If we have seen this error we may check this following

1. Check whether xps is disabled?

  We will run the query to see if the value is set to 1

select name,value from sys.configurations where name= ‘Agent XPs’

2. If the value is set to 0 then we will execute the following statements to enable the configuration.

sp_configure ‘show advanced options’,1
go
reconfigure with override
go
sp_configure ‘Agent XPs’,1
go
reconfigure with override
go
sp_configure ‘show advanced options’,0
go
reconfigure with override
go

3. Restart SQL Agent.

Even though we followed the above steps still agent is showing disable and not able to browse the agent.

Here is some interesting observation.

  1. Checking the error log file, we found there is a process “SQLAgent –Email loader” change the value to 0 after SQL agent restart.
  2. Verified any store procedures or anything running during agent restart and nothing is found.
  3. Check the SQL Service startup parameter and found T3608 is added.

This trace flag will bypass recovery for all databases including system databases except master database so the change values not reflect when agent startup and agent values are goes back  to default value which is 0.

  1. Remove this trace flag from startup parameter and restart the agent. Once SQL agent restart is completed the disable message has disappeared and we were able to browse the agent.

Please check any startup parameter for troubleshooting any SQL server issue.