One thing you'll learn (if you have not already) is that SQL Server 2005's Maintenance Plans (which are actually SSIS workflows executed by SQL Server Agent Service) do NOT provide useful error information. I had to set up a script for a customer but spent my (very) early morning tearing out all the hair on my ageing head.

See, I got this from SQL Server History: Failed:(0) Alter failed for Server 'servername'. If that rings a bell for you then congratulations. It did not for me.

Today I stumbled upon the solution - thanks to a guy named Brent_Dorsey from sqlug.be:

Re: Check DB failed I experienced this exact same issue.

By performing a trace while the maintenance plan was executing I found that the following line of code being executed by the maintenance plan was causing the error:

EXEC sys.sp_configure N'user options', 0 RECONFIGURE

When I execute the statement above in a query window I got the following more detailed error:

Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install. Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

I tried to figure out how to update the code inside of the maintenance plan so that it would execute the following code instead but couldn’t figure out how:

EXEC sys.sp_configure N'user options', 0 RECONFIGURE WITH OVERRIDE

My solution was to go into the Server Properties, click on the Processors tab and check the box next to “Automatically set I/O affinity mask for all processors”

After I enabled the “Automatically set I/O affinity mask for all processors” option my maintenance plan completed successfully.