Home > Configuration, SQL Server > 2005 – Alter failed for server XXX

2005 – Alter failed for server XXX

February 13, 2006 Leave a comment Go to comments

That’s exactly what i thought!! What the hell does that mean?

Well, for me this occurred in the context of running the maintenance plan integrity check task. It had been running fine for weeks but suddenly boom every time it ran it would fail with

"Alter failed for server XXX".

There was no obvious reason for this error so i had to dig out profiler to see what was going on. It turns out that one of the first statements executed by the plan is "sp_configure ‘User Options’,xxxx" followed by "RECONFIGURE".  The reconfigure statement was then generating an error which caused my problem.  But why was it generating an error? That was because a script had been run on the server that changed the "Allow Updates" value from 0 to 1. Changing the value back to 0 resolved the problem.

BOL states that changing the Allow Updates option has no effect since you can’t update system tables in 2005 but it clearly does have an effect since the behaviour of reconfigure changes.

There’s a few points to be taken from this.

1) There actually is no benefit to changing "Allow Updates" so make sure you remove it from your scripts, i have.
2) Why was the "sp_configure ‘User Options’, xxxx" followed by "RECONFIGURE" being executed anyway? Its not relevant to integrity checks and why is it updating server wide settings? I noted the setting was simply set to what it already was, fortunately.
3) Bear this little gotcha in mind because who knows where you will first meet the problem and what message will you get?

Advertisements
Categories: Configuration, SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: