Troubleshooting the following Error:
2017-06-01 18:00:00.000 Logon Error: 17189, Severity: 16, State: 1. 2017-06-01 18:00:00.000 Logon SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: xxx.xxx.xxx.xxx]
What I found that many times experiencing this error in SQL Server’s Errorlog, that “Error: 17189” was only a symptom, and that many times the root cause was the line above in SQL Server’s Errorlog.
In the first case shown below, in SQL Server’s Errorlog listed right above the “Error: 17189”, listed were I/O stalls occurring. In this case, the Storage Administrator found that an I/O switch to the SAN was overloaded, which contributed to SQL Server freezing up. Which no logins would occur on the database instance for the next minute.
In this case, the memory was set to high (less of 40% of the memory was used), and the “lock pages in memory” option was not set. The following error kept appearing in SQL Server’s Errorlog :
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): xxx, committed (KB): xxx, memory utilization: 39%.
By correcting the “max server memory” setting, and setting in the group policy editor to “lock pages in memory”, the “Error: 17189” disappeared.