The Perils of Downsizing Third Party Systems

The request came in to virtualise a SQL Server / SSAS / web reporting system that was provided by a third party. An initial attempt to do this was performed by p2v-ing the existing servers. Everything seemed to be fine until the SQL Server instance was started. The server appeared to start correctly, but the SQL Agent wouldn’t start, SSMS claiming that the Agent XPs were disabled. Attempting to enable the Agent XPs in the usual way:

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'Agent XPs', 1

This failed, badly and worryingly with the following message:

Msg 5832, Level 16, State 1, Line 2
The affinity mask specified does not match the CPU mask on this system.

What seems to have happened is that the third party provider of this system had set an Affinity Mask of 63, limiting SQL Server to using six of the available eight CPUs; unfortunately, the virtual environment was restricted by licensing to only four CPUs.

Changing the affinity mask should be relatively straightforward under normal circumstances:

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'affinity mask', 1

However, this fails with the error message above.

A little Googling pointed me to the accepted answer to a question on the MSDN Social Forums, the gist of which is:

  1. Restart SQL Server in single user mode
  2. Connect using the Dedicated Administrator Connection
  3. update the sys.configurations table directly, setting the value to 0 for the parameter “affinity mask”
  4. restart SQL Server

However, this misses out a couple of potential “gotchas”:

  • Affinity IO mask
  • 64-bit affinity masks – these exist for use in systems where there more than 32 processors

So if you find yourself in this situation, it’s worth updating all the options by using:

UPDATE sys.configurations
SET value = 0
WHERE name LIKE 'affinity%'

Just in case… 😉

This entry was posted in SQLServerPedia Syndication and tagged , , . Bookmark the permalink.

One Response to The Perils of Downsizing Third Party Systems

  1. Perry Whittle says:

    This problem was compounded by the fact the ESX host was only licenced for 4 way SMP 😉
    Could have resolved this by adding back the extra CPUs otherwise

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.