Table of Contents
- Introduction
- Root Cause of the Error
- What to do
- Restore from Backup
- Rebuild the Transaction Log
Introduction
Transaction logs in SQL database are the vital component consisting of all the changes a user makes in database. Moreover, user can undo the changes that he made in a file of the database using the information contained in the
transaction logs; this operation is also a transaction. It can be understood by the following:
You have made some changes in the data file of a database, say DB1, it means that a transaction is taking place. The log begins keeping record by the time you start making changes till you perform commit of that transaction. Note that a database consists
of minimum one data file and physical transaction log.
Now imagine that there is damage to transaction log, it will directly impact the database. If an operation is performed in SQL that requires processing or reading the transaction log, an error may occur similar to the following:
Error: 9004
An error occurred while processing the log for database. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Root Cause of the Error
The error occurs in case of damage to contents of transaction log. The severity of the error is same as that of a database corruption. Therefore, for in-depth analysis of cause, the similar techniques should be applied that are required for database corruption.
What to do
Try out the following to work around this problem:
Restore from Backup
It is recommended to bring the backup into use. It might be the case that the backup of transaction log or its portion has created corruption in the contents of transaction log. In this case, Error 9004 may occur while restoring. It indicates that there
is damage to transaction log placed in the backup.
Rebuild the Transaction Log
If it is not possible to restore from the backup, you can rebuild the transaction log. However, you need to be aware of all positives and negatives of doing it so that you can avoid possible transactional consistency loss in the database. Furthermore, it
includes
DBCC CHECKDB command to execute.
However, you might not be able to carry out the task successfully or even if you do, it might not give the expected results. If so, you can use commercial SQL recovery application. Such applications are the programs built by software experts. More of it,
such software will perform recovery, which has been failed by DBCC CHECKDB command, it will also recover the database from suspect mode. You can also recover the database in case of severe damages.
- Remove From My Forums
-
Question
-
Hi,
i need to attach my db, but i receive this error:
an error occurred while processing the log for database ‘xyj’.
Could not open database ‘xyj’ . Create database is aborted. (Microsoft Sql Server,
error 9004).
Can you help me?
Answers
-
You have two options:
- You can create an empty database with the same name and physical file layout, shut down the server, swap in the files you want to attach in place of the empty DB files, and start the server. The database should come up in suspect mode. You can then ALTER DATABASE <foo> SET EMERGENCY to put it in emergency mode, and then run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. This will pull as much data as possible out of the log to make the database consistent, but may have to delete some data in order to make the database consistent. This is the option which is most likely to get the maximum data back.
- You can attempt to use the CREATE DATABASE FOR ATTACH_REBUILD_LOG to see if that will bring it back. If the database was cleanly shut down, you MIGHT be able to succeed. There is also the chance that the database will be inconsistent or corrupt if there are transactions which could not be rolled back. You should in any event run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS to make your database consistent. In this event, SQL Server will make no attempt to mine information from the log. It will ignore the contents of the log. If there were transactions in process no rollback will be possible, so the ALLOW_DATA_LOSS will be required.
- Remove From My Forums
-
General discussion
-
I am using SQL-Server 2000 . when i try to attach the mdf file it gives error message Error 9004: an error occurred while processing the log for database . please help me out..
All replies
-
Refer this url.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/48cf82c9-2179-46f3-b009-11416a90d248/attach-database-failed-error-9004
Srinivasan
-
Its a known issue …try below:
Create an empty SQL server database with same name & layout.
Now shutdown the services of SQL server.
Move the database file into newly created empty database file that you want to attach.
Start the SQL server database.
Probably, your database will go in suspect mode.
Now ALTER your database and set database in emergency mode.
Run DBCC CHECKDB command on your database with and repair clause, It will give you a repair clause and again run DBCC CHECKDB with repair_allow_data_loss. You will probably loose some data or records of your SQL server database.
Please click the Mark as answer button and vote as helpful if this reply solves your problem
- Remove From My Forums
-
General discussion
-
I am using SQL-Server 2000 . when i try to attach the mdf file it gives error message Error 9004: an error occurred while processing the log for database . please help me out..
All replies
-
Refer this url.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/48cf82c9-2179-46f3-b009-11416a90d248/attach-database-failed-error-9004
Srinivasan
-
Its a known issue …try below:
Create an empty SQL server database with same name & layout.
Now shutdown the services of SQL server.
Move the database file into newly created empty database file that you want to attach.
Start the SQL server database.
Probably, your database will go in suspect mode.
Now ALTER your database and set database in emergency mode.
Run DBCC CHECKDB command on your database with and repair clause, It will give you a repair clause and again run DBCC CHECKDB with repair_allow_data_loss. You will probably loose some data or records of your SQL server database.
Please click the Mark as answer button and vote as helpful if this reply solves your problem