SQL SERVER – FIX : ERROR : Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration

Yesterday I came across following error while enabling fill factor for my database server, when I was trying to write article SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor.

I ran following T-SQL script and it gave me error.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO

In result pan following error showed up.

Msg 5834, Level 16, State 1, Line 1
The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

Fix/Solution/Workaround:

Add option OVERRIDE along with RECONFIGURE command.

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
WITH OVERRIDE
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

2 thoughts on “SQL SERVER – FIX : ERROR : Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration

  1. Hi Pinal,

    Brilliant post as ever, the ‘WITH OVERRIDE’ option has sorted out my problem.

    What does the actual error mean though?
    How about a post demystifying Affinity Mask , I/O Mask and parallelism?

    rgds
    rich

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s