SQL Server logon failure occurs frequently (Error: 17189, Severity: 16, State: 1.)

Question

SQL Server logon failure occurs frequently (Error: 17189, Severity: 16, State: 1.)

Hi,

[Environment]
Microsoft Windows Server 2012 R2 Standard

The maximum server memory of SQL Server for the DB server is set to 4096(MB).
---systeminfo---
Total physical memory: 8,191 MB
Available physical memory: 1,168 MB
Virtual memory: Maximum size: 13,162 MB
Virtual memory: available: 3,419 MB
Virtual memory: in use: 9,743 MB

[Questions]
It frequently happens that DB connections cannot be established when accessing the database.
The API we are using is SQLConnect.

 sql_rtn = SQLConnect (edp->hdbc,
                   (SQLTCHAR *) dsn, (SQLSMALLINT) strlen(dsn),
                   (SQLTCHAR *) user, (SQLSMALLINT) strlen(user),
                   (SQLTCHAR *) pass, (SQLSMALLINT)strlen(pass));

The event occurred 16 times from April 20 to August 21, 2012. The frequency of these events is 1-2 times a week.
However, the intervals are irregular and may occur two days in a row.

Output error log when an ODBC connection cannot be established:.

Function=[SQLConnect] Code=[SQL_ERROR] Nativ=[26] Status=[08001] msg=[Microsoft][ODBC Driver 11 for SQL Server] Client unable to establish Common causes include client attempting to connect to an unsupported The version of SQL Server, the server is too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
Function=[SQLConnect] Code=[SQL_ERROR] Nativ=[233] Status=[08001] msg=[Microsoft][ODBC Driver 11 for SQL Server]Shared Memory Provider: the other end of the pipe There is no process in
Function=[SQLConnect] Code=[SQL_ERROR] Nativ=[26] Status=[08001] msg=[Microsoft][ODBC Driver 11 for SQL Server]Client unable to establish connection
Function=[SQLConnect] Code=[SQL_ERROR] Nativ=[233] Status=[08001] msg=[Microsoft][ODBC Driver 11 for SQL Server] Client unable to establish connection due to prelogin failure

The following error is printed in the event log of Sqlserver when the event occurs.
---The following error is output in the event log of Sqlserver when an event occurs.
Error 2020/08/12 19:10:13 MSSQLSERVER 17189 Logon SQL Server could not start a thread to process a new login or connection (error code 0xc0000000). Check the SQL Server error log and Windows event log for information on possible related problems. Client: <local machine>]

The SQL SERVER error log seems to be the same as what is output in the event log.

2020-08-12 19:10:13.36 Logon error: 17189, severity: 16, status: 1.
2020-08-12 19:10:13.36 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.

What are the causes related to the occurrence of the event, and how can we avoid or investigate the root cause?

Thank you for your help.

Answer 1

Hi @悠 高島 ,

Duplicated?

Reference my reply for your case: https://learn.microsoft.com/en-us/answers/questions/82741/sql-server%E3%83%AD%E3%82%AF%E3%82%AA%E3%83%B3%E4%B8%8D%E5%8F%AF%E3%81%8B%E9%A0%BB%E7%99%BAerror-17189-severity-16-state-1.html

If the reply is helped, please do "Accept Answer".
BR,
Mia

Answer 2

2020-08-12 19:10:13.36 Logon error: 17189, severity: 16, status: 1.
2020-08-12 19:10:13.36 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.

This means that SQL Server is experiencing worker thread starvation or something is creating heavy load in SQL Server causing it not to spawn new threads. The reason should be in SQL Server errorlog can you run sp_readerrorlog and post the relevant part here. Look for paging in SQL Server.

Please use query in this link to capture accumulated wait stats and post output here.

Also please post the output of select @@version.

If server has 8 GB RAM why have you only give 4 GB to SQL Server, are there any other applications running ?

Answer 3

some points to start with in the meantime:

The maximum number of simultaneous connections issue

The maximum number of simultaneous connections allowed on an instance of SQL Server is limited. Your application might be poorly developed and it "leaking connections", meaning that your app opens connections without closing them.

in this case the error in the server side should look like

The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

and the application side error might be:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

Solution/explanation

You can check the MAX_CONNECTIONS in your server and check the value. if the value is more then twice the amount of expected connections, then you probably have issue in your application! I recommend to reduce the value usually so you will get error instead of hide it while eating your machine resources.

/******************* Get MAX_CONNECTIONS */  
SELECT @@MAX_CONNECTIONS  
GO -- default value is 32767  
  
/******************* Change MAX_CONNECTIONS */  
-- Allow to show/manage the server advanced options  
EXEC sp_configure 'show advanced options', 1;    
GO    
RECONFIGURE ;    
GO  
-- Get MAX_CONNECTIONS: check the value under "config_value". If this 0 then you use the default  
EXEC sp_configure 'user connections'  
GO  
-- Change MAX_CONNECTIONS  
EXEC sp_configure 'user connections', 100 ;    
GO    
RECONFIGURE;    
GO   
-- Get MAX_CONNECTIONS  
EXEC sp_configure 'user connections'  
GO  

max server memory issue

Check max server memory configuration and confirm that you do not limit the memory use to a lower value than you can. Configure the value or check it using the same procedure as above using the sp_configure and the value 'max server memory'

Always On availability groups issue

If you are using Always On availability groups then check the Availability Group properties for the routing URL. Wrong port for example might raise the same error.

... more will come...

This error is generic and can raise as result of many issues, let's start with the above and wait for more information if these are not your case

Answer 4

Hi,
We have asked our users to do the following and are reviewing their progress

(1) SQL Server has insufficient number of worker threads.
the number of threads in use at the time of the event ==>> double the value at the time of the event.
and check if the frequency of the problem will decrease by expanding to

(2) Server's maximum memory problem
"SQL Server memory consumption" setting (currently 4GB) ==> expanded to 6GB and the memory of the machine