• Hi Jim

    Mr. Jetson's script should work to solve the problems with the Agent XP's being disabled. The problem here is that you have 2 problems.

    When you enable Agent XPs, you're essentially changing the "Configured Value" of "Agent XP's" from 0 to 1. When you run RECONFIGURE, you're asking SQL to make all the "Configured Values" on your server to their "Run Values" where possible. This RECONFIGURE applies to all configured values.

    Your second problem here is that you have AWE enabled on this server. If you check sp_configure you'll see that the "Configured Value" is 1, but the "Run Value" is 0. So when you run RECONFIGURE, SQL tries to enable the Agent XPs and also enable AWE. Your AWE isn't working because your SQL Server service account doesn't have the OS-level "Lock Pages In Memory" privilege.

    -- Confirm that AWE configured_value = 1, but run_value = 0. Assuming confirmation, proceed with below steps.

    EXEC sp_configure 'awe enabled'

    -- If so, temporarily turn off AWE so that its error doesn't block other configurations

    EXEC sp_configure 'awe enabled', 0

    -- RECONFIGURE will now not be "blocked" by the erroring AWE setting

    RECONFIGURE

    -- Reset the AWE value to its original value. Note that AWE isn't fixed, you're just putting things back where they were.

    EXEC sp_configure 'awe enabled', 1

    -- Go fix the AWE problem

    -- http://technet.microsoft.com/en-us/library/ms190673(v=sql.90).aspx

    -- http://technet.microsoft.com/en-us/library/ms190730(v=sql.90).aspx