Sql-server – SQL Server Express Freezes for a couple minutes

amazon ec2sql serversql-server-express

I have SQL Server Express on an AWS micro instance. These instances have only 615MB of RAM. For the most part it performs fine. My application can make up to about 15 db connections. However, every once in a while it just freezes for a couple of minutes.. my own application logs show all kinds of query time-outs, new db connection attempts failing because of an incorrect pwd (even though I am sure the attempt is using the correct pwd). But after a couple minutes everything turns out fine again.

I have checked eventvwr.msc logs but it doesn't reveal anything. I know there are not backups running at the time. And I have inspected SQL Server logs from SQL Server Management Studio but these too don't reveal anything.

Any ideas? Am I pushing the limits of SQL Server? I have read before the Express Editions run fine until certain connection thresholds get met. Could the number of db connections be an issue?

Best Answer

There is couple of important point missed

Please check whether Auto Close option is set to true for database. I have seen lots of similar issue where Auto close option was culprit.

Is OS resifing on Virtual machine or Physical ?. If its is virtual look for any VM snapshot backup happeing this might also cause freezing.

Other thing can be, do you have backup taken for database using third party tool/windows native backup if so these tools freeze data files for couple of sec to sync metadata and take consistent backup.And you can see VIRTUAL_DEVICE in errorlog when you specifically look ta backup information in it.

Hope this helps

Related Solutions

SQL Server Express – Maximum Connections Allowed

It seems with Windows 7, you can have up to 20 concurrent connections but no more.

If you need more, you'll have to put your SQL Server installation onto a Windows Server OS.

Also see this other SO question on the same topic - SQL Server Express does not limit the number of max connections (which have a 32'768 limit on any edition of SQL Server)

Sql-server – Why does SQL Server 2012 Express use 9.5GB of RAM on the server

SQL Express is limited to 1GB for the Buffer Pool, but there are many other memory pools in SQL Server. What I find surprising is the excessive use of non-buffer memory pools. To find out memory usage per memory clerk, run this:

SELECT type, SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

Hope this helps

Best Answer

Related Solutions

SQL Server Express – Maximum Connections Allowed

Sql-server – Why does SQL Server 2012 Express use 9.5GB of RAM on the server

Related Question