5

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?

Update

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).

The 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%.

Improve this question
3
  • Can you please look in SQL Server errorlog by running sp_readerrorlog and add relevant contents in the main question. Without suitable error we cannot provide you solution. Make sure auto close is set to false for the databases.
    – Shanky
    Commented Sep 14, 2015 at 10:58
  • Is SQL Server the source of the problem or a victim of something else going on with the OS? Without more detail, can't really tell if you're under resource pressure or if the app is misbehaving. In addition to Shanky's suggestion, can you also get perfmon data on your disks? If you're churning the disk, pretty much everything else comes to a grinding halt.
    – SQLmojoe
    Commented Sep 18, 2015 at 0:50
  • Check auto-close on all databases. Also, does your anti-virus have the facility to exclude the directory where your mdf/ndf/log files are? If your antivirus is set to scan such a location then normal DB activity can provoke an antivirus scan.
    – Dave Poole
    Commented Apr 9, 2018 at 9:28

1 Answer 1

Reset to default
0

Do you have SQL Server Profiler installed? Try running a trace to capture any queries that may be causing excessive locks or resource problems throughout the day.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.