INF: Using DBCC REBUILD_LOG in SQL Server 7.0 and 2000
The information in this article applies to:
· Microsoft SQL Server version 7.0
· Microsoft SQL Server 2000 (all editions)
SUMMARY
DBCC REBUILD_LOG is an undocumented command that you use to reconstruct a new log if the current log is unavailable. For example, if you lose the current log file because of a hardware failure or if the current log file is accidentally deleted, DBCC REBUILD_LOG is designed to build a new log. This article discusses how to use this command in SQL Server 7.0 and SQL Server 2000.
WARNING: You should use DBCC REBUILD_LOG with caution in all instances. Please consult a Support Professional trained in Data Recovery or your Technical Lead before you attempt to use this command.
MORE INFORMATION
You should use DBCC REBUILD_LOG as a last resort and the customer should restore a consistent backup if at all possible.
You can use DBCC REBUILD_LOG when the transaction log for a database is lost or damaged. In such a case, it is very likely there are unapplied transactions lost with the log, and the database is therefore inconsistent. Also, the event that causes the loss of the transaction log, such as a hardware failure, may have damaged the database. Thus, after DBCC REBUILD_LOG completes successfully, it is imperative that you run DBCC CHECKDB in single-user mode to check for physical data inconsistencies. After you verify the physical consistency of the database by running DBCC CHECKDB, please check the database for logical data inconsistencies as well. Because a new log has been built, it is very likely that the unapplied transactions in the old log are lost and hence you must verify the logical consistency of the data as well.
All of the same data integrity and corruption issues described in the following Microsoft Knowledge Base articles still apply in SQL Server 7.0 and 2000, which you should consider carefully before attempting to rebuild the transaction log:
Q159356 FIX: DBCC REBUILD_LOG Works Starting With SQL Server 6.5 SP2
When the log file is lost, the database is marked suspect in the SQL Enterprise Manager (SEM). When you try to access the database from the SQL Query Analyzer or the SEM you get the following error:
Server: Msg 945, Level 14, State 2
Database '<db_name>' cannot be opened because some of the files could not be activated.
The errorlog usually shows the following error message:
File C:\MSSQL7\data\<log_filename> does not exist - unable to activate.
DBCC REBUILD_LOG is an undocumented command and you should only use it under the direct supervision of Microsoft Product Support Services. The following steps outline the procedure to rebuild a transaction log for a SQL Server 7.0 or SQL Server 2000 database using the DBCC REBUILD_LOG command:
1. Back up the .mdf/.ndf and .ldf files.
2. Change the database context to Master and allow updates to system tables:
3. Use Master
4. Go
5. sp_configure 'allow updates', 1
6. reconfigure with override
Go
7. Set the database in Emergency (bypass recovery) mode:
8. select * from sysdatabases where name = '<db_name>'
9. -- note the value of the status column for later use
10. begin tran
11. update sysdatabases set status = 32768 where name = '<db_name>'
12. -- Verify one row is updated before committing
commit tran
If you run DBCC REBUILD_LOG without setting the database in Emergency mode, the command does not work. You do not receive an error, but the log is not rebuilt either.
13. Stop and restart SQL server.
If you run DBCC REBUILD_LOG without recycling the server, the following message displays:
Server: Msg 5023, Level 16, State 2, Line 1 Database must be put in bypass recovery mode to rebuild the log. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
14. The syntax for DBCC REBUILD_LOG is as follows:
DBCC REBUILD_LOG('<db_name>','<log_filename>')
where <db_name> is the name of the database and <log_filename> is the physical path to the new log file, not a logical file name. If you do not specify the full path, the new log is created in the Windows NT system root directory (by default, this is the Winnt\System32 directory).
If the file is placed in the Winnt\System32 folder, you can move it to a different folder by running the following commands from the SQL Query Analyzer:
sp_detach_db '<db_name>'
Go
Move the log file over from the Winnt\System32 folder to the preferred folder.
sp_attach_db '<db_name>', '<data_file_name>', '<log_filename>'
Go
If a log file with the same name as specified in DBCC REBUILD_LOG already exists in that directory, then the following message occurs:
Server: Msg 5025, Level 16, State 1, Line 1
The file 'C:\MSSQL7\Data\<log_filename>' already exists. It should be renamed or deleted so that a new log file can be created.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
You will need to specify a different filename or rename or delete the existing one.
15. Rebuild the log with this code:
16. DBCC TRACEON (3604)
17. DBCC REBUILD_LOG('<db_name>','<log_filename>')
Go
If the command is successful, the following message appears:
Warning: The log for database '<db_name>' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.
After the log is successfully rebuilt, the database is placed in DBO Use Only mode. That is, the status of the database is 2048 irrespective of what the status was previously. You must reset the status using sp_dboption or through the SEM.
18. Set the database in single-user mode and run DBCC CHECKDB to validate physical consistency:
19. sp_dboption '<db_name>', 'single user', 'true'
20. DBCC CHECKDB('<db_name>')
21. Go
22. begin tran
23. update sysdatabases set status = <prior value> where name = '<db_name>'
24. -- verify one row is updated before committing
25. commit tran
Go
26. Turn off the updates to system tables by using:
27. sp_configure 'allow updates', 0
28. reconfigure with override
Go
WARNING: After verifying the consistency of the database by running DBCC CHECKDB, and fixing any errors, please make sure to check the database for logical consistency as well. Because a new log has been built, the transactions in the old log are lost, hence you must also verify the logical consistency of the data as well.
After you successfully complete the preceding steps, you may use the database as normal.
