SQL Server Performance

SQL Server 2005: Attach (or Reattach) a Database Missing the LDF

Discussion in 'SQL Server 2005 General DBA Questions' started by pavelcc, Jan 5, 2011.

  1. pavelcc New Member

    I had a database where my ldf where corrupted. I was getting error as
    SQL SERVER – FIX: Error 945 Database cannot beopened due to inaccessible files or insufficient memory or disk space. See theSQL Server error log for details
    I have tried many things and nothing worked. Then I figured out that thereis a work around.
    1. I have stopped the SQL Server.
    2. Rename the mdf file.
    3. Restarted the SQL server
    4. Dropped the database
    5. Created another Database with the same name.
    6. Take the database off line
    7. Dropped the new mdf and ldf files
    8. Rename back to the original mdf file that you have renamed at step 2
    8. Then followed steps as bellow.
    Now you need to attach a database that has no log file (.LDF), or havedeleted the log file because it was too big and need to reattach the database,SQL Server 2005 does allow you to do this. You can attach the database byfollowing way:

    1. Take the database off line
    2. Open the SQL Server Management Studio and connect tothe desired server.
    3. Right-click on Databases and select Attach.
    4. Click the Add button and select thedatabase file to be attached.
    5. Under Databases to Attach rename attach as to a newname
    6. Under database details, select the.LDF file and click the Remove button.
    7. Click OK
    8. A new database will be created with the old mdf file and a new ldf file
    9. Drop the original Off line DB
    10. Renamethe new database as the old database.
    Everything looks good now
  2. Luis Martin Moderator

    Welcome to the Forums.!
    Thanks for sharing.
  3. satya Moderator

    Welcome to the forums.
    The steps you have detailed must be followed as a last resort, but still the problem occurs due to the resource contention.
    The first task to check is SQL Server error log, and also make sure that .mdf and .ldf file are not marked as read only on operating system file system level. Check this for SQL 2000 databases http://msdn.microsoft.com/en-us/library/aa258746(v=sql.80).aspx

Share This Page