Archive for the ‘Affinity Mask’ Category

The Problem

When I tried to deploy our SQL Maintenance plans to a server nothing happened.
I then checked the server and go the error: The affinity mask specified does not match the CPU mask on this system.

I ran sp_configure and saw that advanced options were not turned on. I wanted to turn them on so I could set the affinity mask properly. When I tried to do this I got the error: The affinity mask specified does not match the CPU mask on this system. I ran sp_configure again and I saw that the configured value was set to 1 but the run value was set to 0. This happened even I fI used Reconfigure with Override.
I restarted the instance to see if that would help. It didn’t.
I tried to change the affinity mask through Properties on the Instance in SSMS. It was all greyed out so I was unable to change anything.

Solution

I won’t detail all the things I tried which didn’t work, but there were many.
I changed the server start-up parameters to include –f. This starts the server in single user mode with limited configuration.
I also stopped the SQL Agent Servers as I didn’t want it taking my single connection.
There was an application which connects as soon as SQL starts. This was local so I couldn’t disable TCP/ip or use ipSec to stop it getting in. The solution was simpler – I disabled the account as it was using a SQL Login.
I then restarted SQL.
In SSMS I created a DAC connection to the box by going to File > New Query > then connected as
ADMIN:Servername
I ran sp_configure and could see that affinity mask had a configured value of 2 but a run value of 0 – due to the limited configuration start up. I ran sp_confiure and set it to 0. I did the same with the I/O affinity mask.
I then removed the –f start up parameter and restarted SQL. I re-enabled the SQL Login I’d disabled and started up the SQL Agent.
All was now fine.

Why did it happen?

This was a VM with only one CPU. I assume the affinity settings were put in place on a physical box. The machine was then virtualised with one CPU and the problems started.

Advertisements