none
FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred w

    Question

  • Hello all,
    I am running into an interesting scenario on my desktop.  I'm running developer edition on Windows XP Professional (9.00.3042.00 SP2 Developer Edition).  OS is autopatched via corporate policy and I saw some patches go in last week.  This machine is also a hand-me-down so I don't have a clean install of the databases on the machine but I am local admin.

    So, starting last week after a forced remote reboot (also a policy) I noticed a few of the databases didn't start back up.  I chalked it up to the hard shutdown and went along my merry way.  Friday however I know I shut my machine down nicely and this morning when I booted up, I was in the same state I was last Wenesday.  7 of the 18 databases on my machine came up with

    FCB:Surprisepen: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf'. Diagnose and correct the operating system error, and retry the operation.
    and it also logs  
    FCB:Surprisepen failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).

    I've caught references to the auto close feature being a possible culprit, no dice as the databases in question are set to False.  Recovery mode varies on the databases from Simple to Full.  If I cycle the SQL Server service, whatever transient issue it was having with those files is gone.  
    As much as I'd love to disable the virus scanner, network security would not be amused.  The data and log files appear to have the same permissions as unaffected database files.  Nothing's set to read only or archive as I've caught on other forums as possible gremlins.  I have sufficient disk space and the databases are set for unrestricted growth.

    Any thoughts on what I could look at?  If it was everything coming up in RECOVERY_PENDING it's make more sense to me than a hit or miss type of thing I'm experiencing now.
    Monday, December 03, 2007 4:25 PM

All replies

  • It seems that Sql Server is unable to access the database files at startup....could you pls once again check the permissions and provide modify privilege to the folder containing database files to the startup account.....also ensure that antivirus is not accessing these files

    - Deepak
    Monday, December 03, 2007 4:32 PM
    Moderator
  •  

    I am having this same problem; a sequence of errors appears in the error log (see below*).

     

    There are no other processes accessing the file(s).  I even turned anti-virus off for a moment to try to restart.  All files & folders in the path are NOT read-only.  I even tried DELETING all the ERRORLOG files & letting it re-create them itself.  Same result.  PLEASE HELP!!!

     

    (Also of note: I'd had no problems with this installation before now--it used to start just fine--but I just COPIED the MSSQL/LOG and MSSQL/Data folders to an alternate location [outside the "Program files" path, which is--IMNSHO--a stupid default, for many reasons], and set the startup parameters accordingly [which are obviously correct to some degree, because it IS looking in the correct location for these files based on the info in the error log].)

     

    Thanks in advance for any help,

    -pt

     

    *error sequence in error log:

    2008-07-01 21:14:08.90 spid5s      Starting up database 'master'.
    2008-07-01 21:14:09.20 spid5s      Error: 17207, Severity: 16, State: 1.
    2008-07-01 21:14:09.20 spid5s      FCB:Surprisepen: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Data files (HEAR)\MSSQL\LOG\ERRORLOG'. Diagnose and correct the operating system error, and retry the operation.
    2008-07-01 21:14:09.20 spid5s      Error: 17204, Severity: 16, State: 1.
    2008-07-01 21:14:09.20 spid5s      FCB:Surprisepen failed: Could not open file C:\Data files (HEAR)\MSSQL\LOG\ERRORLOG for file number 2.  OS error: 32(The process cannot access the file because it is being used by another process.).
    2008-07-01 21:14:09.20 spid5s      Error: 5120, Severity: 16, State: 101.
    2008-07-01 21:14:09.20 spid5s      Unable to open the physical file "C:\Data files (HEAR)\MSSQL\LOG\ERRORLOG". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".

    Wednesday, July 02, 2008 7:21 AM
  •  

    More info:  In my log_[#].trc file, I get these under the ErrorLog EventClass:

     

    Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.

    Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

     

    Maybe this is the root of the problem (although, if so, the messages in ERRORLOG are misleading, or--at best--simply not very informative)--but how does one "Run the RECONFIGURE statement"?

     

    Thanks,

    -pt

    Wednesday, July 02, 2008 7:37 AM
  • Hi Charles \ PT,

     

    Make sure mdf and ldf files are excluded from antivirus scanning.

    Do you have any startup procedures that will do something on those db's?

     

    If you still receive that error you can manually attach the database using sp_attach_db procedure or through GUI. Try manual way of attaching the db and let me know whether you are able to bring your db online.

     

    I hope Agent Xp's is not related with this error.

    Wednesday, July 02, 2008 1:22 PM
    Moderator
  • In my case, I excluded the folder contaning DB files from the scanning for anti-virus and it worked
    Monday, January 18, 2010 6:14 PM
  • Had similar issue and was able to fix the issue by adding the Log On account (service account) under which our sql server 2k5 is running under - to the root folder 'SQL2005' (typically the path is as D:\SQL2005\MSSQL.1\MSSQL\Log\). This issue typically occurs if the locations for the data and log files were changed since the intial sql installations and the required permissions on the intial folders are not carried on to the new folders.



    • Proposed as answer by Dileep Gonti Wednesday, May 12, 2010 7:10 PM
    Thursday, March 18, 2010 1:07 AM
  • "Had similar issue and was able to fix the issue by adding the Log On account (service account) under which our sql server 2k5 is running under - to the root folder 'SQL2005' (typically the path is as D:\SQL2005\MSSQL.1\MSSQL\Log\). This issue typically occurs if the locations for the data and log files were changed since the intial sql installations and the required permissions on the intial folders are not carried on to the new folders."

    I am experiencing the same problem with a SQL 2005 Express server. What permissions should the Log On Account have to the root SQL2005 folder?

    Tuesday, April 13, 2010 8:10 AM
  • Had similar issue and was able to fix the issue by adding the Log On account (service account) under which our sql server 2k5 is running under - to the root folder 'SQL2005' (typically the path is as D:\SQL2005\MSSQL.1\MSSQL\Log\). This issue typically occurs if the locations for the data and log files were changed since the intial sql installations and the required permissions on the intial folders are not carried on to the new folders.

    hi ... i do have same problem .. can u tell me what ar eteh permissions should have that service acnt ...

    any help will be appreciated ...

    thanks in adv

     

    Wednesday, May 12, 2010 7:09 PM
  • I had a similar problem. Whenever I restart the server, 3 out of about 10 databases would not start. Rest would start fine. SQL Log would show the OS file Access Error for these 3 databases mdf files. Finally I was able to resolve the problem.

    I had SQL 2000 and SQL 2005 both running on my server. On both these I had 3 common database names which were hence not starting. SQL 2000 starts before SQL 2005 and hence it would not allow SQL 2005 to open the 3 databases which were common between the two and thus giving File Access Error.

    In Windows Task Manager under the Processes Tab, check if you have 2 instances of Sqlserver.exe running. Then you probably have the same problem as mine.

    Switch off one of the instances i.e. SQL 2000 or SQL 2005 or some other version of SQL which ever you are not using from auto starting on reboot and your problem should be resolved.

    Thursday, September 16, 2010 8:48 AM
  • When this happens >> try to find out who hs taken the lock on the file .You can download process explorer from Microsoft site (http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) ..

    Open it >> in the menu , select find >> Find handle or DLL >> copy the complete filename with path (for example C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Test.mdf ) and you wil see who has taken the lock on the file handle ..

    Most probably it might be an anti-virus or a 3rd party too to monitor SQL or backup SQL databases .

    HTH


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, September 16, 2010 3:21 PM
  • I have a similar problem with SQL 2008 SP2 and the lock is from SQL itself.

    I have a maintenance plan that every 1 hour backups then transaction log and it seems that sometimes something goes wrong and the file becomes locked.

    Moreover on that db every 1 hour a dbcc checkdb is run without being in any maintenance plan.

    Paolo

    Thursday, December 30, 2010 11:50 AM
  • There is no problem with the Rights and Installations and all this things.

    There is problem with path you choose for .mdf and .ldf files.

    please search the .mdf files and .ldf file that you have outside  "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" location for same database you want to attach

    then you can easily attach and detach files.

    Monday, March 19, 2012 6:06 AM
  • Same problem here however, I read up on Red Gate that this needs to be address from the server where the database is located.   The fix that worked was restoring the last saved restore file, then setting up the restore originally needed.

    R, J

    Thursday, June 06, 2013 2:15 PM