2

My SQL Server 2008 R2 database suddenly cannot be accessed. When I try to view it in SQL Server Management Studio, it appears something like below:

Error Screen Shot

It cannot be detached and attached. I need to 'Take Offline' and then 'Bring Online' to solve it. This problem happens around once a week.

After checking the error logs, the following are the records:

Error: 18456 Severity: 14 State: 38.
FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWRCMS.mdf for file number 1.
OS error: 32(The process cannot access the file because it is being used by another process.).
Error: 17204 Severity: 16 State: 1.
Starting up database 'PWRCMS'. Login failed for user 'sa'.
Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.7.68]
Error: 18456 Severity: 14 State: 38. Login failed for user 'sa'.
Reason: Failed to open the explicitly specified database. [CLIENT: 172.16.7.68]
Error: 18456 Severity: 14 State: 38.
FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWLCMS.mdf for file number 1.
OS error: 32(The process cannot access the file because it is being used by another process.).
Error: 17204 Severity: 16 State: 1.
Starting up database 'PWRCMS'.
Starting up database 'PWLCMS'.

Is there any solution to prevent this happening?

Improve this question
0

4 Answers 4

Reset to default
4

Unknown,FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWRCMS.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).

Your database is in use from another process. You need to identify what that process is. Perhaps you attach the DB with AttachDatabase connection string option to a different SQL Server Express instance?

Improve this answer
1
  • Thanks for the advice. Will look into it on this direction. The database accessed by some different task scheduler exe. May be that is the reason.
    – DragonZelda
    Jan 21, 2016 at 9:15
2

The database could have the auto close option enabled, which means it's closed when the last user logs off, in order to save resources. On a SQL Server Express instance with lots of other things happening maybe this is a solution, but even then I would recommend against it.

When the database is closed, an antivirus product might begin scanning it, and thus locking it. You can use the Sysinternals tool ProcessMonitor to see what is locking it.

Improve this answer
1

Suddenly? Was there any issue prior to this ,like space issue for log file or something? As at times when there is disk space issue causes the dbs to go into this state where a server reboot helps.

The error is self explanatory and something is holding the file and not allowing sql server to have the exclusive access.

Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\PWRCMS.mdf for file number 1.
OS error: 32 (The process cannot access the file because it is being used by another process.).

In my case, I disconnected all users from the server and tried to restart the instance which still threw same error. Last resort was to restart the server which fixed the issue by removing any process that was hindering the db to come online.

Improve this answer
0

As @Aaron Bertrand SQL Blog Aaron Bertrand SQL Blog to Troubleshooting Error 18456.

The Error 18456 generally means a failed login attempt. Like other error messages, it also has a State number and there are different State numbers associated with it, each having a different meaning for the failure.

Error: 18456 Severity: 14 State: 38 

Reason: 
    Either Failed to open the database specified in the login properties.
    or
    Cannot open database "<database>" requested by the login. The login failed.

Error 18456 generally means a failed login attempt. Like other error messages, it also has a State number and there are different State numbers associated with it, each having a different meaning for the failure. Like in your case state is 1 and state: 38.

As Error: 17204 Severity: 16 State: 1.

As per MSDN BOL solution number 5 Here

If you are getting the

The process cannot access the file because it is being used by another process

This error Use a tool like Process Explorer from Windows SysInternals to find out if another process or service has acquired exclusive lock on this database file Stop that process from accessing SQL Server Database files In a cluster environment, make sure that the sqlservr.exe process from the previous owning node has actually released the handles to the database files.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.