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?

4

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.

  • 1
    +1 for pointing to the antivirus, it might actually be the issue here. – Massimo Feb 8 '16 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 '16 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 '16 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 '16 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).

  • 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 '16 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.

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.

  • 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. – hot2use Oct 7 '16 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.

0

When SQL Server Database in pending mode then you can get it with the manual method. When you restore the database, please add WITH RECOVERY at the last restore sentence. RESTORE DATABASE dbname WITH RECOVERY. You can also get the solution from here - https://community.spiceworks.com/how_to/157233-how-to-fix-recovery-pending-state-in-sql-server-database

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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