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.
Case 1:
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.
Case 2:
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.