I had been working on a website based on ASP.NET 2.0 and SQL Server 2005 Express Edition. However I was facing a problem on it. Problem was that the website worked all right during the busy hours such as in the morning and afternoon but in the wee hours of the night the website would hang i.e. website did not give response at all and some how the website used up all the server memory and processing speed.
The problem was peculiar in that it happened when there was no traffic on the website and thereafter it worked fine. I ignored it thinking it might be due to the server maintenance that usually went on at that time however when this started to happen frequently and the client began to lose patience I knew this had to be tackled soon.
So I looked at the SQL Server logs. I found nothing in it. Then I looked at the web server logs thinking some one may be trying to use brute force or DOS attack on the website. I again found nothing. This started to become confusing.
Out of nowhere I thought why not look at the Event Viewer and surely I saw that the SQL Server database was being closed at some time and then it was opened. I deduced that the time when it was opened must be the same time at which the website did not respond and caused that hanging effect.
I knew that SQL Server had an auto-close feature but this feature was by default unchecked in SQL Server 2000 where as after going through Books Online I found that it was by default checked in SQL Server 2005 due to which I faced this problem.
I unchecked the Auto Close option in SQL Server 2005 and the problem was resolved.