March 17, 2010 at 12:50 pm
Background I moved an instance to a different drive
I moved system and user databases over
SQL Server is running and working with the exception of SQL Server Agent
The error is:
Agent XPs disabled
Log:
2010-03-17 11:18:47.71 spid56 SQL Server blocked access to procedure 'dbo.sp_sqlagent_get_startup_info' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.
2010-03-17 11:18:47.74 spid56 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-03-17 11:18:47.74 spid56 Configuration option 'Agent XPs' changed from 0 to 0. Run the RECONFIGURE statement to install.
2010-03-17 11:18:47.76 spid56 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2010-03-17 11:25:23.28 spid56 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-03-17 11:25:23.28 spid56 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
I go into Surface Area Configuration and make sure the service is enabled
I also run the following:
sp_configure 'show advanced options', '1'
reconfigure
go
sp_configure 'Agent XPs', '1'
reconfigure
go
Still get the same error in the log.
I ran this as well:
declare @agent_enabled bit
declare @show_advanced bit
select @show_advanced = cast(value_in_use as bit)
from sys.configurations where name = N'show advanced options'
select @agent_enabled = cast(value_in_use as bit)
from sys.configurations where name = N'Agent XPs'
if 0 <> @agent_enabled
begin
if 1 <> @show_advanced
begin
exec sys.sp_configure @configname = N'show advanced options', @configvalue = 1
reconfigure with override
end
exec sys.sp_configure @configname = N'Agent XPs',@configvalue = 0
reconfigure with override
if 1 <> @show_advanced
begin
exec sys.sp_configure @configname = N'show advanced options', @configvalue = 0
reconfigure with override
end
end
I checked the user and changed the user account that starts the service and still a no go. This is a clustered environment. Thanks.
March 27, 2012 at 12:17 pm
Hi There,
You can try adding the sql agent account as "Lock Pages in memory" in Group Policy editor. Once done that, restart the node and try enabling Agent Xps after that. I am clueless as to how it runs but it worked for my team mate,
March 28, 2012 at 1:24 am
what is the error in the windows application event log?
April 2, 2012 at 12:09 pm
without asking too many why's and what "move" means I am taking a leap.
since the drive changed maybe it is a simple thing - give he following script a try:
---
--- fix_msdb_subsystem.sql
---
delete from msdb.dbo.syssubsystems
---
exec msdb.dbo.sp_verify_subsystems 1
---
--- restart the agent now
---
--- end of fix_msdb_subsystem.sql
---
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply