# CPU / Affinity mask problem in SQL 2005

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

-
@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. –  RobM 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 –  RobM 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:

2. Verify your database backups are valid
3. Really, make sure you have good backups of your databases
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.