TransWikia.com

SQL Server 2008 R2 Temporary Login Issue

Server Fault Asked on January 18, 2021

We have a mature SQL Server 2008 R2 server, being used from many C# web applications, each with connection pooling.

Last night, all web applications lost the ability to login to the database for 6 minutes, before the issue resolved itself. This was for a variety of logins.

I’ve had a look at the event log on the server, and found a lot of messages like:

The client was unable to reuse a session with SPID [Various], which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

I could not find a failed operation immediately before the error message. The failure ID of 29 apparently refers to RedoLoginException.

There were also plenty of these in the event log:

Login failed for user '[Various]'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection.

Also some time-outs:

A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.
Timeout occurred while waiting for latch: class 'DBCC_MULTIOBJECT_SCANNER' id ..., type 4, Task ...: 44, waittime 300, flags 0x1a, owning task .... Continuing to wait.
Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', ...

and:

IO Completion Listener (0x900) Worker ... appears to be non-yielding on Node 1. Approx CPU Used: kernel 0ms, user 0ms, Interval; 15334

From the point of view of the client web servers, they received a number of login errors:

Logon failure: the user has not been granted the requested logon type at this computer
Logon Failure: The target account name is incorrect
Logon failure: unknown user name or bad password

I wondered about thread pooling, and found that max worker threads is set to 0.

Any ideas?

UPDATE: This has now happened on three occasions.

One Answer

This is most likely related to the method of connection by the clients and the connection pooling from MSSQL. The application would probably need to update the connection strings or it's something very specific to your overall setup. You can have a look over here https://dba.stackexchange.com/questions/41820/connection-pools-being-reset-with-error-18056-severity-20-state-46-perfm.

By the way, it's easier for everyone to give an answer if you have specific error description from MSSQL.

Hopefully this helps.

Answered by Alex H on January 18, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP