SQL 2005 Database will not attach after moving Log file

Posted on 2012-09-22
Last Modified: 2012-09-24
I recently had a problem with our SQL 2005 Database eating up all disk space with log files. I detached one of the problem databases and copied the log to a new location and deleted the old log. When I go to attach the database back again it tells me that it cannot find the log so I point it to the correct location and continued but received an error.

An error occurred while processing the log for database '<databasename>'. If possible, restore from backup. If backup is not available, it might be necessary to rebuild the log. Could not open new database '<databasename>'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error:9004)

What can I do to make this work? I have a database backup but if I do a restore I am going to run into the same problem as when I first detached the database and moved the log. What do i need to do to correct this problem?
Question by:tparus
    LVL 69

    Accepted Solution

    first, if your log is too big and never shrinks, it is surely because your recovery model is not to simple and you never backup the database? is that your case?

    now if you want to reattach the database without the actual log file, check
    LVL 3

    Expert Comment

    It really depends.  First thing, backup both the datbase files and log files for sure.  Do you know if your log file has been commited?  If you are pretty confident that it has, then attach without the log so it will create a new one.  You can do this with Management Studio.  When you go to attach, it will show both the database files and log file.  Click the log file and at the bottom right, there is a delete.  Delete the log and click OK.  It will attach and delete the log file.

    The other option is to use a stored procedure to attach and rebuild the log.  To do this, use the sp_attach_single_file_db stored procedure.  More information can be found at

    Otherwise, copy the files back to the original locations and try to attach.  If it attaches successfully, I would then run a dbcc checkdb against the database just to make sure there are no issues.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now