asked on

SQL 2005 Database will not attach after moving Log file

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?

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dave_tiller

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.