none
Problems with 'locked' MDF File RRS feed

  • Question

  • Hi Folks,

    When I create a Database and try to copy the MDF file, I get a  message to say that the Database is locked or in use by another user.

    Seems like maybe I am not doing a 'Save' operation - although I can't find one ?

    If I shut down the PC and restart it, then the MDF File is freed up, although when I try to open it, there is something wrong with it.

    I'd appreciate any suggestions.

    Thanks a lot.

     

    Barry

     

     

     

     

    Thursday, November 16, 2006 12:23 AM

Answers

  • The database is in use by another user, it's being used by SQL Server. SQL Server locks the database file while it is attached to the server, which can cause issues when trying to do file copies. You need to detach the database from the server before trying to copy or move the file. You also need to consider copying/moving the log file (.ldf) with the databases, SQL Server uses both of these files. If the database has been correctly closed and detached, you usually don't need to move the log wih the data.

    If you plan to copy your database a lot, you can set the AutoClose property to True for the database, this results in the database being closed when there are no connections for the database. Closing a database is different that detaching it, but it will allow you to make a copy of the file. In SQL Express, databases created using the CREATE DATABASE command are already set with AutoClose=True, but if you create the database in management studio, the AutoClose property will be False and you will need to set it manually by accessing the database properties.

    Mike

    Thursday, November 16, 2006 5:10 AM
    Moderator

All replies

  • The database is in use by another user, it's being used by SQL Server. SQL Server locks the database file while it is attached to the server, which can cause issues when trying to do file copies. You need to detach the database from the server before trying to copy or move the file. You also need to consider copying/moving the log file (.ldf) with the databases, SQL Server uses both of these files. If the database has been correctly closed and detached, you usually don't need to move the log wih the data.

    If you plan to copy your database a lot, you can set the AutoClose property to True for the database, this results in the database being closed when there are no connections for the database. Closing a database is different that detaching it, but it will allow you to make a copy of the file. In SQL Express, databases created using the CREATE DATABASE command are already set with AutoClose=True, but if you create the database in management studio, the AutoClose property will be False and you will need to set it manually by accessing the database properties.

    Mike

    Thursday, November 16, 2006 5:10 AM
    Moderator
  • Mike,

    Thanks a lot for what looks like a perfect answer.

    I'll be able to try it later on today.

    I also appreciate the speed of your response.

    Thanks again.

    Regards

    Barry

     

     

     

    Thursday, November 16, 2006 9:27 AM
  • After a day faffing around with a similar problem I found your advice and it works - great!

     

    Sunday, November 4, 2007 8:03 PM