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.
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....
ReplyDeleteIt shouldn't get disabled unless you select the Automatic option to set processor affinity mask.
ReplyDeleteThese 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.