Home / server / Questions / 129678
Accepted
Rob Moir
Asked: 2010-04-07 01:46:43 +0800 CST

CPU / Affinity mask problem in SQL 2005

  • 772

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 
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'affinity mask', 0x00000000
GO
RECONFIGURE 
GO

-----------------------------------------

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.
sql-server sql-server-2005

2 Answers

  1. Best Answer

    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.
    • 1

  2. 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.

    • 1