Hi Recently we faced SQL 2008 R2 SP2 Memory overloaded problem It appears there is an issue running a query on the DB that is preventing users from logging into DB. Deadlock Issue also Happening Every Day at Peak Hour. Kindly find the attachment for log file ![alt text] : /storage/temp/2505-error-log.jpg How to resolve this situation and overcome? Any Advice would be highly appreciated!!!!!!!!!!!!!
Looking at the screenshot you posted, the main reason for the problem is below- Error: 18456, Severity: 14, State: 23. Login failed for user. Reason: Access to server validation failed while revalidating the login on the connection. Quoting Aaron Bertrand's [Troubleshooting Error 18456] blog There could be a few reasons for state 23. The most common one is that connections are being attempted while the service is being shut down. However if this error occurs and it is not surrounded in the log by messages about SQL Server shutting down, and there is no companion reason along with the message, I would look at KB #937745, which implies that this could be the result of an overloaded server that can't service any additional logins because of connection pooling issues. Finally, if there **is** a companion reason, it may be the message indicated to the right, indicating that SQL Server was running as a valid domain account and, upon restarting, it can't validate the account because the domain controller is offline or the account is locked or no longer valid. Try changing the service account to LocalSystem until you can sort out the domain issues. :