SQL Server 2005 Configuration

[Bohica69]

I've got a problem with my SQL Server 2005 configuration and I'm hoping that someone here (Mike?) can help me.

Our production box started live on (I think) a quad core machine. For some reason our ops guys set the processor affinity. I have NO idea why they did it - but that's not the issue now. After the machine was setup - it was virtualized - and migrated to one of our virtual machines - everything is working ok.

Now the problem is I need to enable the CLR integration becasue I need to do some queries that require regular expressions. When I try and execute the following command
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
I get this message:
The affinity mask specified does not match the CPU mask on this system.

Ok, so now I'm stuck. So I poke around in the Master db and take a peek at sp_configure. Turns out it's querying the sys.configurations view. Ok, so I do a select and see the affinity mask has a value of 14. Ok. Further looking at the sp_config reveals this line:
EXEC %%ServerConfiguration( ConfigID = @confignum ).SetValue( Value = @configvalue )

Ok, I'm thinking - just change the confignum and configvalue to what I need it to be and it should work right? No dice when I try to run that from the Query window I get a message:
Incorrect syntax near '%'. The command I'm trying to run is:
DECLARE @confignum SMALLINT
DECLARE @configvalue INT

SET @confignum = 1535
SET @configvalue = 0

EXEC %%ServerConfiguration( ConfigID = @confignum ).SetValue( Value = @configvalue )

So is it possible to run that Exec? Or is there something I'm missing? I REALLY don't want to reinstall SQL server with ALL that headache - If I can just get that value changed to 0 (which from what I understand means no set affinity) then I think I'd be golden. Any ideas?

Thanks.

 

[mikeblas]

Running SQL Server in a virualaized machine is a pretty bad idea.

I'm not sure why you're not using sp_configure directly to set the affinity mask directly, through the documented interface:

-- tell SQL server to use a mask of 0
sp_configure 'affinity mask', '0';
GO

-- reconfigure to effect that change
RECONFIGURE
GO

and that should make the change you want.

 

[Bohica69]

Unfortunately I can't run

sp_configure 'affinity mask', '0';
GO

Because it's an "Advanced Option" and the sp_configure sproc throws

The configuration option 'affinity mask' does not exist, or it may be an advanced option.

And I can't run

sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE

to turn on the advance options because 'The affinity mask specified doesn't match the CPU mask on this system' and according to BOL - further sp_configure changes are not accepted.

So at this point I'm stuck between a rock and a hard place - if the sp_configure would let me set the affinity i'd be fine, but it won't. It's a chicken and the egg problem.

I've discovered that the sys.configurations view looks at the syscurconfigs and sysconfugres tables - I see the value I need to change, however ad hoc queries to system tables are not allowed. In doing more research I've discovered that it appears I MIGHT be able to do the adhoc IF I can get the SQL server in single user mode and I use the DAC (both of which I have 0 experience with). Here's the link I found about this: http://forums.microsoft.com/msdn/showpost.aspx?postid=89594&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

This is like working on a car, being able to see the bolt in the engine compartment you need to remove - but not being able to get the wrench on it.....

Any ideas?

 

[mikeblas]

Oh, I see.

Yes, the admin connection is the way to fix this.

1) NET STOP MSSQLSERVER to turn off your service.
2) run SQLSERVR.EXE directly on the command line from a command prompt window. Start it with the -m option
3) start another command window
4) Use "SQLCMD -A -E" to connect to the local SQLServer instance with the admin channel
5) Once the output from the SQL Server server instance dies down, you should be able to do the sp_Configure command to change the affinity.
6) RECONFIGURE
7) exit from SQLCMD
8) CTRL+C in on the SQLSERVR process. It will ask if you want to quit; say yes and it will shut down gracefully
9) NET START MSSQLSERVER to start your service again

That should get you started. The rub is that you still need to change to show advanced options. You can paly around with this a bit, but if it doesn't work I think you'll have to reinstall to get a master database that's rebuilt to not have the bad affinity setting.

 

[Bohica69]

Mike, thanks for the clear and consise instructions (once again)

Still didn't work.

One last thing to try is to set the number of procs in the virtual machine higher temporarily and see if that makes a diff. If not.. Sigh - reinstall time. suckage.

Thanks again

 

[mikeblas]

Yep; if you can get the VM to match the mask, temporarily, that would help.