SQL SERVER – Unable to Start SQL – Error: SQL Server Could Not Spawn Lazy Writer Thread

I have a tendency to try out various settings on a typical SQL Server just to see how the behavior changes from time to time. While playing with affinity settings in SQL Server – I shot myself in the foot. It was interesting to reach how I came out of the situation. I was not able to reproduce this error on the enterprise edition though.

Here is what I found in Errorlog:

2015-07-28 17:12:11.31 Server    Processor affinity turned on: node 0, processor mask 0x00000006. Threads will execute on CPUs per affinity settings.

2015-07-28 17:12:11.31 Server    I/O affinity turned on, processor mask 0x00000001. Disk I/Os will execute on CPUs per affinity I/O mask/affinity64 mask config option.

2015-07-28 17:12:17.44 Server    Node configuration: node 0: CPU mask: 0x00000006:0 Active CPU mask: 0x00000006:0.

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.
If we convert highlighted number to binary, we can decode the affinity which I have set.

0x00000006 (in hex) = 0110 (in binary) in Processor affinity
0x00000001 (in hex) = 0001 (in binary) in I/O affinity

You can treat 1s and 0s as checkboxes for processors. Below picture should make it more clear.

As you can see there is no overlap so I don’t see any problem, but still SQL Server didn’t start. I checked various online places, but few articles asked to repair the instance, which will not work in this situation. So finally, I sat down calmly and recalled that there is an option to start SQL with minimal configuration. I have used that earlier with one of my clients for SQL startup due to tempdb misconfiguration.

Here are the steps which worked:

1. Start SQL in minimal configuration using –f parameter.

NET START MSSQLSERVER /f

If you are having named instance called Inst1 then you need to use below
NET START MSSQL$INST1 /f

2. Connect to SQL using any client tool (SQLCMD or SSMS)
3. Change the affinity back to default.

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


UI

4. Stop SQL Service

Default Instance – NET STOP MSSQLSERVER

Named Instance – NET STOP MSSQL$INST1

5. Start it normally.

In some situations, you might run into issue where you would get this error while following above steps.

Login failed for user ‘LoginName’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

You can refer my friend Balmukund’s blog to make a connection in single user mode via start up parameter “m”

Help: How to fix error – Reason: Server is in single user mode. Only one administrator can connect at this time

Once you are able to connect using SQLCMD, you need to use T-SQL to fix the affinity value. Hope this blog helps you to solve one of the things I discovered by my experiments. Do let me know.

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