Sql-server – CPU / Affinity mask problem in SQL 2005

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

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