June 28, 2015 at 7:17 am
Had some big problems with my SQL Server 2008 R2 SP1 during the last maintenance running on Windows Server 2008 R2 Enterprise ( upgraded from Standard). I'm getting the following error,
"SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' 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."
I did a google search and found others with the problem but the resolution isn't working for me. I ran the following commands, heck I ran them multiple times. No errors come back running these commands. But the same error keeps happening, I even restart the service and then the server again.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
here is what happened.
The msdb got too big for the disk it was on. I had maintenance plans to clear out the all of histories but I didn't think of the mail items. When I finally saw this problem I tried clearing them out using the stored procedures only with it finally failing because the transaction log got full. Since we didn't have a lot of jobs scheduled I decided to just recreate the msdb. So I scheduled a maintenance window, download SQL Server Service Pack 3.
my steps are as follows:
->diff backup of msdb
->stopped the service and started it again with: NET START MSSQLSERVER /T3608
->detached msdb with: SQLCMD -E -SP-SRVR-SQL-01 -dmaster -Q"EXEC sp_detach_db msdb"
->moved it off the drive
->restarted the service without any startup flags
->recreated msdb with: SQLCMD -E -SP-SRVR-SQL-01 -i"D:\Microsoft SQL ->Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb62715.out"
I looked at the log file and there were no actual error messages so I applied service pack 3 and restarted server after it installed, was actually prompted to restart. In my tests I was never prompted to restart the server. I used this as a guide: https://msdn.microsoft.com/en-us/library/dd207003(v=sql.105).aspx
Now I never started the agent service after recreating the msdb database since I knew it wasn't on the same version. I just went directly to the latest SP. Our applications are running fine but now I cant do scheduled backups.
Has anyone encountered this problem before or can lend me any assistance on how to fix this? Been searching online for hours now and nothing is helping me. Everything keeps coming back to running those reconfigure statements.
June 28, 2015 at 8:06 am
Ok I got it working now but it doesn't make sense to to me. I ran the sp_configure commands again this time using 0 for the 'Agent XPs'. Started the agent to see if there would be a different message but it started up just fine.
So I ran exec sp_configure and looked at the 'Agent XPs', and it seems to be configured.
name minimum maximum config_value run_value
Agent XPs0111
I reran
sp_configure 'Agent XPs', 1;
reconfigure;
and got this response, the same as before.
"Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install."
since I got this running Im going to take advantage and get my backups scheduled but I'm still trying to figure out what happened. this is the strangest thing I've seen my server do.
Anyone else encounter this behavior with sp_configure?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply