Why is my database not available?

Question

I have one database on my server that constantly becomes unavailable. To fix, I take it offline, then back online. Looking at the event viewer there are three errors in quick succession:

FCB::Open failed: Could not open file C:...\MyDatabase.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).

FCB::Open failed: Could not open file C:...\MyDatabase_1.ldf for file number 2. OS error: 32(The process cannot access the file because it is being used by another process.).

A file activation error occurred. The physical file name 'C:...\MyDatabase_1.ldf' may be incorrect. Diagnose and correct additional errors, and retry the operation.

Looking at the database properties, the log file path looks correct. I'm not really a DBA but play one out of necessity. What else should I be looking for here?

(SQL Server 2008 Express SP1)

Solution

As Randolph said, you likely have an AV process holding onto the files in question. To confirm this, get Process Explorer or handle.exe (or both!) from the Sysinternals at Microsoft. Learn and love all of of their tools.

If it's not AV, but something else that has those files locked, then fix whatever it is.

Also, as an aside, you don't want data or log files on your system volume (typically the C:\ drive). If they fill the disk, you've taken your server down. You probably want them on a different volume. Caveat - with SQL Express, your max DB size may be lower than the amount of free space you'll ever have on the disk, but do you want to count on that always being true?

Comments

[Admin]

Looks like you have an antivirus that is locking the file. Add an exclusion.

[Artur Peniche]

SQL Server itself was locking the file files. Database was set to AUTO_CLOSE ON. I set it to OFF and no further issues (yet).

[Artur Peniche]

Thanks for the tip about file locations. This is a smallish database on a dev server, but taken under advisement.