SQL 2005 Database will not attach after moving Log file

Posted on 2012-09-22
Medium Priority
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:Tim
LVL 70

Accepted Solution

Éric Moreau earned 2000 total points
ID: 38425110
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 http://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

Expert Comment

ID: 38428793
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 http://msdn.microsoft.com/en-us/library/ms174385.aspx.

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

864 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