2

We have quite a few programs of our own design which communicate to our SQL Server (SQL Server 2014 Express). Every now and then they start blowing up all over the place; we run around trying to figure out what's wrong, only to open up SQL Server Management Studio and find that our database isn't running.

To be more specific: we open SSMS, expand the "Databases" node and it says:
[DATABASE NAME] (Recovery Pending)

Opening the SQL Server Configuration Manager and restarting the service for the down database fixes the problem and everything starts to work again... for a while.

After running across this problem a few times, I've noticed that it only happens after we reboot the server- though not every time. Meaning that sometimes when the server boots up, SQL fails to do the same. An interesting fact is that we have multiple databases running on the SQL server and they don't go down together; one of the databases might say "(Recovery Pending)" while the other one works fine.

I have not been able to find a solution to this problem- or even a cause. Any ideas?

EDIT:

After going through the server's event logs I found this:

Log Name: Application Source: MSSQL$SQLEXPRESS Event ID: 17204 Task Category: Server Level: Error Keywords: Classic Description: FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\DuraDB.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).

What would be causing the file to lock, especially after a reboot?

Improve this question

5 Answers 5

Reset to default
5

Have you got an on-access antivirus scanner running? Make sure you've got your exclusions set up correctly. Microsoft has documented this in KB309422. Also note that there are known issues with some products by McAfee and Sophos.

Improve this answer
4
  • 1
    +1 for pointing to the antivirus, it might actually be the issue here.
    – Massimo
    Feb 8, 2016 at 14:25
  • The only antivirus I have on the server is Windows Defender. I added the "DATA" folder to the exclusions list so we'll wait and see if that solves the problem.
    – Keith Stein
    Feb 9, 2016 at 15:22
  • Just looked at the antivirus again. It turns out that the there hasn't been a virus scan in over 30 days because the server has never been "idle" enough. Seeing as the problem happened again recently it doesn't seems like the antivirus is behind this one.
    – Keith Stein
    Feb 9, 2016 at 15:29
  • 2
    @KeithStein Windows Defender has both an on demand scanner (which is what the history shows) and an on access scanner (aka, "real-time protection"). The on access scanner will automatically lock and scan any data file when a program tries to open it. Since SQL Server data and log files are so large, it's not at all uncommon for the scanner to take longer than SQL Server's timeout. That's why the exclusions are important.
    – Bacon Bits
    Feb 9, 2016 at 15:36
3

A recovery is typically performed by SQL Server upon startup when a database hasn't been shut down cleanly, f.e. because SQL server crashed, or because the server experienced a power outage; if this is not the case, then this could be caused by filesystem problems, disk issues or driver issues with storage controllers. You should check the system logs for unexpected shutdowns, or for disk troubles.

Also, make sure your Windows system is up to date with the latest updates, and SQL Server is updated to the latest service pack (SP1 for SQL Server 2014).

Improve this answer
1
  • I updated SQL to the newest version, Windows is totally up to date. The problem is still occurring. I found something pertinent in the event logs and added it to my original question.
    – Keith Stein
    Feb 8, 2016 at 14:19
0

Go to Sql configuration manager > Sql Server Services > Select Service type (Sql Server) right click go to properties Select Logon as Check on This account and make sure your Account name is "NT SERVICE\MSSQL$SQLEXPRESS" then press start.

Improve this answer
0 
The Express versions automatically shutdown after a period of inactivity.     
There is a special sp_configure value you can set to increase the timeout.
Express Editions only have the 'user instance timeout' config item available.
The default is 60 minutes.

exec sp_configure 'show advanced options', 1
reconfigure
go
exec sp_configure 'user instance timeout'  -- show current value
go

/*
exec sp_configure 'user instance timeout', 65535  -- set to max timeout 
reconfigure
go
*/

Alternatively, your application could run a simple "heartbeat" query every 10 minutes or so to keep SQL Server Express alive. SQL Server Express doesn't allow SQL Agent to be used, so a scheduled job wouldn't work. You could also schedule a sqlcmd.exe job using Windows Task Scheduler.

Improve this answer
1
  • This would be a valid answer if the instance of SQL Express were running in RANU mode (Run As Normal User). Otherwise this option is probably not active. RANU has to be activated with sp_configure 'user instances enabled','1' msdn.microsoft.com/en-us/library/ms254504(v=vs.110).aspx OP could check if this option is on with a simple sp_configure or by querying the sys.configurations table.
    – John K. N.
    Oct 7, 2016 at 13:14
0

Internally SQL Server was restoring the database pending operations or transactions. We should wait for a couple of minutes and automatically SQL Server will restore the database.

This happened because the developer restarted the service when SQL Server was performing a big transaction operation. There can be also another reason like Transaction log file is very large.

I have prepared a full article on this problem, you can visit this article here.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .