February 2006 - Posts

2005 - Alter failed for server XXX
13 February 2006 18:04

Thats exactly what i thought!! What the hell does that mean?

Well, for me this occured 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.

Theres 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?

by ACALVETT | with no comments

Filed under: Maintenance Plans, SQL 2005, Tools, Tips