Recently I have upgraded my development machine to Windows 10 and SQL Server 2014 SP1 Express (12.0.4213).
Then I noticed that my application sometimes crashes with a timeout exception. It is quite a small .NET application with a 100MB database and few simple queries per second. The application is running at the same machine as the server and connects via shared memory.
I tried to connect to the server with SSMS when yet another timeout occurred, and found that the whole server is unresponsive. It took several attempts just to log in and I was unable to start the activity monitor due to timeouts. Then I tried to restart the server and it took about 1 minute to stop it.
The problem occurs several times per day. Sometimes it continues working normally after a timeout, sometimes I have to restart the server and it restarts quickly, sometimes it hangs for a while during restart.
The server never consumes more than 5% of CPU and 300 MB of memory, even during freezes. Usually there are few hundreds MB of free RAM and few GB of swap space available.
There are no errors or warnings in the event log at the time when the problem occurs. Over the last week there were few errors: 17300, 17312, 28709. Not sure if they correspond to one of these timeouts. None of these occurred over last 2 days, but the problem continues to arise.
The same application never experienced any problems with SQL Server 2012 SP1 Express on Windows 7.
How this problem can be fixed?
The application database had Auto Close enabled, I disabled it. Also I had to remove some data, so the database is now about 20 MB.
The timeouts on the application now occur less often (about 1 per day). However, the Activity Monitor in SSMS still timeouts almost always. Actions like viewing the database properties also execute very slowly (it takes about 20 seconds).
sp_readerrorlog command shows nothing except startup info. Example.
I also have monitored the disk activity at the time I got a timeout in the Activity Monitor and the disk load was below 20%.