We are running SQL Server 7 and got the following error message when the first user tried to log in to a database this morning at 6:13:
udopen: Operating system error 32 (The process cannot access the file because it is being used by another process.) during the creation/opening of physical device d:MSSQL7dataCanada_Data.MDF.The database option for auto close was set to 'yes,' so the database was opening and closing every time something ran. Several processes ran during the night, with the last one at 2:15 showing the database had been opened and closed successfully. The 6:13 open failed and when looking at the database through Enterprise Manager the database was listed as 'suspect'. When looking in the data folder the mdf and ldf files were there and looked OK. One of the DBAs has the MSSQL 2000 Enterprise Manager and took the database off line and then put it back on line and it seemed to fix the issue.
My question is can the auto close option cause a problem like this? We have since moved that option to 'NO,' but I'm still concerned there may be something else.
The autoclose option shouldn't create an issue. There are all kinds of things that can happen with a closed database. When a database closes, SQL Server closes the files and releases all handles to those files. That means anything can generate a handle to those files and if something else had an open handle on the file, SQL Server would be prevented from accessing the files the way it needs to access them. In your case, something else had an open handle on the file. Since SQL Server couldn't open the file in a read/write mode, it placed the database into a suspect status.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.