SQL Server Performance

SQL Express database stops responding

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Amit_Kumar, Mar 12, 2010.

  1. Amit_Kumar New Member

    Hi,
    We have a website that runs on SQL express 2005. When we send news letters to our readers, the SQL express database stops accepting connections after some time. For example, before sending the news letter. sp_who2 shows say 80 connections. After the news letter are gone, connections shown by sp_who2 start decreasing and then they stay at a low number around 20. We have tried SQL trace to identify the SQL proc that might be causing this but we have not been able to detect the problem. We have also verified the code executed at the time of sending news letter and it seems fine.

    I can assume that the website load increases after the newsletter but I don't know what could be triggering SQL Express that it stops accepting new connection.

    Is it possible that as soon as the maximum connections reach around 100, the DB stops responding. In all our pages, we close the connection after we access data.

    Can you please help me as to how I can debug this problem?
    Thanks
    Amit
  2. moh_hassan20 New Member

    There is a limitation for sql express : 1 cpu , 1GB RAM , 4GB database size
    Can you check if you reached these limits.
    have alook to: http://www.sql-server-performance.com/tips/asp_sql_server_p1.aspx
  3. Amit_Kumar New Member

    Hi Hassan, Thanks a lot for responding. We have not reached the database size limit. I suspect that the problem is due to number of open connections because the app works fine most of the times and when we do sp_who2 the total number of open connection is around 60 to 70. When this problem happens the number of open connection goes up to 100, 110. Thanks a lot for forwarding the link to the article. We have been following almost all those guidelines.Is there a way to check whether the open-connection count could cause this? Would upgrading to SQL Express 2008 help? Thanks again for your help[quote user="moh_hassan20"]
    There is a limitation for sql express : 1 cpu , 1GB RAM , 4GB database size
    Can you check if you reached these limits.
    have alook to: http://www.sql-server-performance.com/tips/asp_sql_server_p1.aspx
    [/quote]
  4. moh_hassan20 New Member

    This problem may occur due to memory allocation contention which causes a slowdown on the login process or the query execution process.
    what is SP level installed ?
    can you post any error messages in sql server error , to know what is happen.
    Measure memory counters to see if it is in range.
    This looks like a known issue : http://support.microsoft.com/kb/937745

  5. Amit_Kumar New Member

    Hi Hassan, Thanks a lot for your help. SQL express 2005 SP3 is installed on this machine. The hotfix is for SP2, will it work for sp3? The time stamp of files for SP3 is a later date. But SP3 does not fix this memory leak bug in its bug list. We increased the concurrent connection of the database server to first 400 and then to 1000. After increasing the load, this time the app did not crash for a long time. SP_Who2 also showed around 170 to 180 connections to the DB. But then the DB server crashed again. There was no message as such. The web pages would just be waiting endlessly and when I did SP_Who2, the connection count was back to 20 or so. I restarted the database server a few times and then the app started again. It happened twice. RegardsAmit[quote user="moh_hassan20"]
    This problem may occur due to memory allocation contention which causes a slowdown on the login process or the query execution process.
    what is SP level installed ?
    can you post any error messages in sql server error , to know what is happen.
    Measure memory counters to see if it is in range.
    This looks like a known issue : http://support.microsoft.com/kb/937745

    [/quote]

Share This Page