April 3, 2020 at 8:57 am
Recently my sql server hang quite often.
When my sql server hang, i tried to connect to sql server using ssms and received error message "Timeout expired" . Please refer to attachment 1.
My server hang at 3:40pm 03April2020 .
When i check the event log, i found there is alot "Memoryclerk_..." logs captured starting from 3:37pm till 3.50pm 03April2020 before i restart the server. Please refer to attachmentLog1 n attachmentLog2. What is the log indicated?
Can anyone help me please? This occur several times already.
Any helps will be greatly appreciated. Thanks
Regards
Mr Goh
April 3, 2020 at 6:31 pm
Looking at the log it says it out of memory. "Memory allocation failure". You need more memory on your VM or need to allocate your memory differently.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 3, 2020 at 8:10 pm
that being a VM - if Vmware ensure that
and do make sure that the server has enough memory - by the looks of it (and I may be wrong) it looks like the server has 4GB ram only - not much for a SQL Server instance.
April 4, 2020 at 12:24 am
Hi,
Mr Brian n frederico,
I'm not using VMware. It is a physical server with 32GB ram.
I have snapshot the setting for the sql server.
Please help to have a lookup.
Anyway to prevent the server from not responding which will affect the operation.
I have waited for 10min but the sql server still not responding before I restart the server.
Any helps will be greatly apreciated. Thanks
April 6, 2020 at 2:37 pm
Your max memory is set way too high. I would drop that down to a more reasonable value.
As Frederico_fonseca said, you should have at LEAST 1 GB free for the OS and more depending on various thing. For example, if you run SSRS, SSIS, SSAS, antivirus, IIS, basically any other tools, you need to leave some memory for each additional thing you have installed. Giving SQL default memory (2 PB) means SQL is going to eventually use up ALL of those 32 GB that you have leaving near 0 for any other processes.
My preference is to have 4 GB for the OS and antivirus, and more for SSIS, SSRS and SSAS. Then I do not install any other software on there (no IIS, no SSMS, no visual studio, no chrome browser, etc). When I say "more", how much depends on which of those you have installed and how complex the packages/reports are. More complex usually means more memory.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 6, 2020 at 4:42 pm
+1 for what Brian said. Set a max memory. In this case, I'd likely set max memory to 28GB for this machine, which should leave 4. You might also check the target server memory in Perf mon to see what this is reading.
April 8, 2020 at 12:23 am
Hi Brian and Steve,
Thanks for your valuable and precious advice.
Will set the max memory to 28GB and see how it goes.
Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply