Wednesday, October 27, 2010

SSIS job fails:The affinity mask and affinity I/O mask configuration should not conflict

I was called today to help an issue with maintenance plan in SQL Server 2005.The Integrity Job created with SSIS Package was failing. Neither the output file nor Job history had much detail except the error:

The package execution returned DTSER_FAILURE

To debug the issue I decided to capture trace with all events under Errors and Warning selected along with SQL:StmtStarting,SQL:StmtCompleted,SQL:BatchStarting,SQL:BatchCompleted ,RPC:Started
and RPC:Completed events selected.

After analyzing the trace file the error in which I was interested was Error: 5834, Severity: 16, State: 1 and  just below that it had entry

The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

On checking the configuration of the server it was found that  the same processor was affinitize for both SQL Server worker thread scheduling and for I/O processing.

Under this configuration when SQL Server executes RECONFIGURE statements it throws an exception.
I changed these values , set it separately ,restarted SQL Server (and of course SQL Agent)and tried executing  job .The integrity job succeeded without any error.These affinity value shouldn’t be shared for same processors or you should set  Automatically set processor affinity mask for all processors and Automatically set I/O affinity mask for all processors option.


  1. Very good observation...but I wonder MS would have disabled or grayed out the other check box if either of the ones is selected...however, nice post....

  2. It shouldn't get disabled unless you select the Automatic option to set processor affinity mask.
    These option sets processor affinity for IO and other operation and there can be cases where the same processor would be used for both IO and other OS operation.