SQL Server 2008 Hang and not responding

  • pi_goh

    SSC Rookie

    Points: 34

    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

     

     

    Attachments:
    You must be logged in to view attached files.
  • Mr. Brian Gale

    SSC-Insane

    Points: 22902

    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.

  • frederico_fonseca

    SSChampion

    Points: 14685

    that being a VM - if Vmware ensure that

    • Ballooning is disabled
    • Max memory is set correctly on SQL Server instance - and leaving at least 1GB for the operating system to function (more depending on the total memory of the server
    • Memory on the ESX host is not over committed

    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.

  • pi_goh

    SSC Rookie

    Points: 34

    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

     

    Attachments:
    You must be logged in to view attached files.
  • Mr. Brian Gale

    SSC-Insane

    Points: 22902

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719736

    +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.

  • pi_goh

    SSC Rookie

    Points: 34

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply