Q
Problem solve Get help with specific problems with your technologies, process and projects.

Can the auto close option cause problems?

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

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close