As a SQL Server enthusiast, I often find myself experimenting with various settings to understand how they affect the behavior of the database system. Recently, while playing with affinity settings in SQL Server, I encountered an interesting situation that I would like to share with you.

First, let’s briefly discuss what affinity settings are in SQL Server. Affinity settings allow you to control the assignment of CPUs and disk I/Os to specific processes in a SQL Server instance. By configuring affinity settings, you can optimize the performance of your database system by ensuring that certain processes are executed on specific CPUs or dedicated disk I/Os.

In my experiment, I accidentally set the affinity settings in such a way that SQL Server failed to start. Upon checking the error log, I found the following message:

2015-07-28 17:12:17.45 Server    Error: 17120, Severity: 16, State: 1.
2015-07-28 17:12:17.45 Server    SQL Server could not spawn lazy writer thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

After analyzing the error log, I discovered that the issue was related to the affinity settings I had configured. The log provided the hexadecimal and binary representations of the affinity settings, which helped me understand the problem.

For example, the hexadecimal value 0x00000006 corresponds to the binary value 0110 in the processor affinity setting. Similarly, the hexadecimal value 0x00000001 corresponds to the binary value 0001 in the I/O affinity setting.

By visualizing the binary values as checkboxes for processors, I realized that there was no overlap between the assigned CPUs and the available CPUs. However, SQL Server still failed to start.

After researching various online resources, I came across a solution that involved starting SQL Server with minimal configuration. Here are the steps that resolved the issue:

1. Start SQL Server in minimal configuration using the -f parameter:

NET START MSSQLSERVER /f

2. If you have a named instance called Inst1, use the following command:

NET START MSSQL$INST1 /f

3. Connect to SQL Server using any client tool, such as SQLCMD or SSMS.
4. Change the affinity settings back to their default values using T-SQL:

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO;
GO
EXEC sys.sp_configure N'show advanced options', N'1';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'affinity I/O mask', N'0';
GO
RECONFIGURE;
GO
EXEC sys.sp_configure N'show advanced options', N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO

5. Stop the SQL Service:
• Default Instance: NET STOP MSSQLSERVER
• Named Instance: NET STOP MSSQL$INST1
6. Start SQL Server normally.

It’s worth mentioning that in some cases, you may encounter an additional error message when trying to connect to SQL Server in single user mode. The error message states: “Login failed for user ‘LoginName’. Reason: Server is in single user mode. Only one administrator can connect at this time.” In such situations, you can refer to my friend Balmukund’s blog for instructions on how to connect in single user mode via the startup parameter “m”.

I hope this blog post helps you understand the concept of affinity settings in SQL Server and provides a solution if you ever encounter a similar issue. Feel free to reach out to me if you have any questions or if you would like to share your own experiences.

Reference: Pinal Dave (https://blog.sqlauthority.com)