locked
SQL Server 2008 Virtual Memory Issue RRS feed

  • Question

  • We are using SQL Server 2008 on Windows Server 2008, Mirroring is setup on two different servers. The memory consumed by Primary SQL Server 2008 (20GB Database size) increases gradually @ 1GB/Hour for almost 78-100 Connections. This high memory actually stuck the transactions and we need to restart the server.

    This is a very critical problem for us. 

    Could you please help us in this regard.

    Monday, October 4, 2010 4:52 PM

All replies

  • Why do you feel that memory consumed by SQL is the problem? SQL is designed to keep as much memory as possible to avoid physical IO from mdf files. SQL also responds to memory pressurce alert send by OS.

    Ideally, you should restrict max memory server of SQL Server to some limit. Is that a 32 bit or 64 bit?


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, October 4, 2010 5:14 PM
  • Thanks for your reply.

    Actually memory consumption is a problem at a level when it increases to a limit and SQL Server terminate the connections and due to that no connections are refused.

     

    We are using 64 Bit Machine.

     

    Thanks


    Muhammad Akmal
    Monday, October 4, 2010 5:19 PM
  • Whats the error you get when connection is refused? Any error in ERRORLOG?
    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, October 4, 2010 5:25 PM
  • Sorry for the confusion!

    Whenever it rises to 10 GB, application is unable to get connections from available connection pool... 

     

    Thanks in advance for your help.



    Muhammad Akmal
    Monday, October 4, 2010 6:06 PM
  • Application and SQL sitting on same box? There must be error message somewhere (application or SQL).


    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, October 4, 2010 7:38 PM
  • Following is the error in trace log of the application:

     

    Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached

     

    We have monitored general stats of SQL Server 2008, where general connections and logical connections are in between 100-150.

     

    Thanks.


    Muhammad Akmal
    Tuesday, October 5, 2010 5:17 PM