question

ashok2012 avatar image
ashok2012 asked

Memory Overloaded in SQL 2008 R2

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][1] [1]: /storage/temp/2505-error-log.jpg How to resolve this situation and overcome? Any Advice would be highly appreciated!!!!!!!!!!!!!
memorysql2008r2overflow
error-log.jpg (303.9 KiB)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Ashok - What service pack are you running the SQL 2008 R2 version?
4 Likes 4 ·
We are running SQL 2008 R2 SP2
0 Likes 0 ·
nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
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][1] 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. [1]: http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

`+1` *THE* Go-To link for 18456 errors
2 Likes 2 ·
ajain87 avatar image
ajain87 answered
Hi Ashok, has issue resolved or still need assistance?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.