Having a problem with a SQL Server which was virtualised from a physical machine. The CPU mask was set on the physical SQL Server for some reason prior to virtualisation and now advanced options are not available in the machine now it's a VMware guest. So I need to reconfigure the CPU affinity mask settings - which are advanced options, so this is blocked because of the affinity mask issue.

I've tried doing this from the SQL server in single user command line mode, I've googled and found lots of people with similar problems but no real solution.

Sample commands and output from query analyser below.

sp_configure 'show advanced options', 1 
sp_configure 'affinity mask', 0x00000000


Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Msg 5832, Level 16, State 1, Line 1

The affinity mask specified does not match the CPU mask on this system.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'affinity mask' does not exist, or it may be an advanced option.
  • @Robert Moir: can you explain what you mean by this: "The CPU mask was set on the physical host for some reason" - not sure how a setting on the physical host would affect SQL Server on a guest VM. – MattB Apr 6 '10 at 15:35
  • Sorry Matt, you're right that is confusing, I've edited the question to clarify. – Rob Moir Apr 6 '10 at 17:24
  • thanks, understand now. What about going at it from the reverse perspective, and temporarily changing the VM to have the number of procs that SQL Server is expecting? – MattB Apr 7 '10 at 13:10
  • Not a bad idea - we thought of that but the original hardware had 8 procs and VMWare ESX 3.5 supports 4 per guest maximum. Thank you for the suggestion – Rob Moir Apr 7 '10 at 13:25
  • How about changing it to a single proc VM temporarily? – SqlACID Apr 8 '10 at 0:05

I think the Microsoft supported solution would be to backup your databases, reinstall SQL Server and then restore your databases to the new installation.

If you want to try something that is not supported by MS, and may be downright dangerous, you could try to update the master database through a SQL Query. I don't know that I recommend this, but it might work for you and save some reinstallation headaches. Also, I've never done this before, so can't guarantee it will even work. The steps are:

  1. Backup your databases
  2. Verify your database backups are valid
  3. Really, make sure you have good backups of your databases
  4. Stop your SQL Server
  5. Start the SQL Server in Single User Mode (add the -m option to sqlserver.exe)
  6. From an (administrative) command prompt, run the following in sqlcmd:

    sqlcmd -S ADMIN:server\instance -d master -W -s "," -Q "SELECT Name, Value, Minimum, Maximum, Value_in_use, is_dynamic, is_advanced from SYS.Configurations Where Name = 'affinity mask'"

    of course replace the server\instance before running. Also, the ADMIN: at the beginning of the server name is important - this connects you on the dedicated admin connection.

  7. Assuming this returns the value you are expecting (the incorrect value), you can try to update the affinity mask to 0. Do this with the command:

    sqlcmd -S ADMIN:server\instance -d master -W -s "," -Q "update sys.configurations set value=0 Where Name = 'affinity mask'"

  8. Shut down the single user mode server.

  9. Startup SQL Server as you normally would.
  10. Cross your fingers.
  • I can't try your unsupported solution yet but I'll accept on the basis of the supported one, which is what I have reccomended to our management. Given that the server runs a major CRM db for a college, plus things like helpdesk software, etc, I really am not comfortable with veering too far into unsupported-land. Thanks for your help. – Rob Moir Apr 8 '10 at 12:46

We had this exact problem at our shop. In our case we had a 4 cpu SQL 2005 box that P2V'd into a 2 cpu VMWare SQL 2005 box. After SQL 2005 came back online it was caught in the catch 22 position that you describe above. We were able to correct the situation by changing the VMWare box back into a 4-way box (simple change with a quick reboot). In our case, we left it as a 4-way box, but one could have turned off processor affinity...downsized it back to a 2-way and then re-established the processor affinity.

You can query the sys.configurations table to determine the decimal value that your processor affinity is really set at. If you do the conversion from decimal to binary you can then determine what the bit mask looks like and you will likely see that it is set to expect more processors than you actually have.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.