x

Run RECONFIGURE statement to install - SQL 2005

My SQL Job Agent is not running and I'm getting the following error:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

When I execute RECONFIGURE on the master database or msdb, the SQL Agent still won't run. What's the problem?
more ▼

asked Nov 02, 2011 at 01:51 PM in Default

technette gravatar image

technette
1.2k 84 97 108

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Have you tried

RECONFIGURE WITH OVERRIDE
?
more ▼

answered Nov 02, 2011 at 01:57 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left

It looks like your job is attempting to change one of SQL Servers configuration options (using sp_configure). In particular it's attempting to change the value of one of the advanced options.

Take a look at the job and see if it's running exec sp_configure along with one of the following:

Ad Hoc Distributed Queries

affinity I/O mask

affinity mask

affinity64 I/O mask

affinity64 mask

Agent XPs

awe enabled

blocked process threshold

c2 audit mode

cost threshold for parallelism

cursor threshold

Database Mail XPs

default full-text language

default trace enabled

disallow results from triggers

fill factor (%)

ft crawl bandwidth (max)

ft crawl bandwidth (min)

ft notify bandwidth (max)

ft notify bandwidth (min)

index create memory (KB)

in-doubt xact resolution

lightweight pooling

locks

max degree of parallelism

max full-text crawl range

max server memory (MB)

max worker threads

media retention

min memory per query (KB)

min server memory (MB)

network packet size (B)

Ole Automation Procedures

open objects

PH timeout (s)

precompute rank

priority boost

query governor cost limit

query wait (s)

recovery interval (min)

Replication XPs

scan for startup procs

set working set size

SMO and DMO XPs

SQL Mail XPs

transform noise words

two digit year cutoff

user connections

Web Assistant Procedures

xp_cmdshell
more ▼

answered Nov 02, 2011 at 02:19 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left

Is the SQL Agent itself not running? If not, and it's refusing to run, then you might need to run the following:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'Agent XPs', 1
RECONFIGURE WITH OVERRIDE

and then try launching SQL Agent.

If the SQL Agent is running, and you're still having problems, then if your job requires the show advanced options setting, then doing as @Magnus Ahlkvist suggests should be sufficient (ie running the first two lines of the query block above).

If the job is starting, but failing to complete, then can you extract the full error message from the msdb..sysjobhistory table and post that up please?

Without the full & detailed errors, however, I'm a little bit stymied.
more ▼

answered Nov 02, 2011 at 02:42 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

(comments are locked)
10|1200 characters needed characters left
I had to re-deploy my SSIS package. Someone cleaned up the database.
This was causing SQL Agent to fail.
more ▼

answered Nov 02, 2011 at 03:10 PM

technette gravatar image

technette
1.2k 84 97 108

What do you mean by "cleaned up the database"? Is this something that you should have been testing for in your SSIS package? Is this likely to happen again? Is it meant to happen, or was someone overreaching their authority, in which case what are you doing to make sure that it doesn't happen again?

These are (mostly) rhetorical questions...
Nov 02, 2011 at 03:21 PM ThomasRushton ♦
A (not so) rethorical question: What do you mean when you say that SQL Agent fail? Does the job fail or does the SQL Agent service fail to start. And which are the error messages? Like @Thomas Rushton points out: msdb.dbo.sysjobhistory is the place to look for error messages if a job is failing. Windows Event log is probably the place to look if the service is failing.
Nov 02, 2011 at 03:52 PM Magnus Ahlkvist

This solution is running on a test database server which was refreshed. Another user did go a little overboard when this test database was refreshed. Since I'm in testing phase, I keep near perfect documentation and all of my scripts so that I can quickly restore.

@Magnus, The SQL Job was failing and not the Agent but the error that showed up in the log was "Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install" ...
Nov 03, 2011 at 07:34 AM technette
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x729

asked: Nov 02, 2011 at 01:51 PM

Seen: 8834 times

Last Updated: Nov 02, 2011 at 01:51 PM