3

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)

1
  • 1
    Looks like you have an antivirus that is locking the file. Add an exclusion.
    – user3914
    Feb 28, 2011 at 18:07

2 Answers 2

2

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?

2
  • 1
    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). Mar 1, 2011 at 14:52
  • Thanks for the tip about file locations. This is a smallish database on a dev server, but taken under advisement. Mar 1, 2011 at 14:53
0

I've been attempting to fix this error for ages too! The various forums on the www don't provide a permanent fix, but it seems to me that the correct solution is to use a process explorer such as EMCO UnlockIt to prove that when the error occurs, the log file or mdf file that your website is trying to open is definitely at that point being blocked by sql server express (even after you have closed the sql server express application). So after you close SQL Server, you still get the error when you log onto your website.

I think the error is wholly accurate. There are not enough processes allowed to access either the log file or the database, or both. If you have the website open on your desktop and it is therefore accessing the database, and perhaps some other user is trying to do so as well (perhaps even to a different database if you have more than one hosted website). Well then you need at least 2 simultaneous processes, and that's before SQL Server has retained one, so you would then need 3 process, and one more as other users connect, more still. I could be wrong I am not an expert on this subject. However:-

If you open up SQLSERVER EXPRESS and right click the root node, you can increase the threshold for blocked processes in the Advanced tab, and you can also increase the maximum number of concurrent connections. It seems to me that this is the solution, anyway I increased my threshold for blocked processes from 0 to 12 and its working fine so far. Perhaps its quite late at night so not many users are trying to connect, but anyway this seems a sensible solution to me!

Good luck. Frankie, Leeds West Yorkshire, UK

You must log in to answer this question.

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