Rebuilding the transaction log

Question

We have a very large database (~6TB), whose transaction log file was deleted. We have tried:

1. Detaching and reattaching the database; and
2. Undeleting the transaction log file

but nothing has worked so far.

We are currently running

ALTER DATABASE <dbname> REBUILD LOG ON (NAME=<dbname>,FILENAME='<logfilepath>')

... but given the size of the database, this will probably take a few days to complete.

My question is: is there a difference between the command above and DBCC CheckDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS)? Should we be executing REPAIR_ALLOW_DATA_LOSS instead?

It's worth noting that the data is derived from other sources so the database can be rebuilt, however we suspect it will be much quicker to repair the database than to reinsert all the data again.

Update:

For those keeping score, the ALTER DATABASE/REBUILD LOG command completed after around 36hrs and reported:

Warning: The log for database 'dbname' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

We then ran a DBCC CHECKDB (took about 13hrs) which was successful.

Answer 1

Never detach the Suspect database again. Anyway, how did you attach the database after detaching it? You used CREATE DATABASE with FOR ATTACH_REBUILD_LOG option?

These commands should have done the trick.

ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

I wrote a post for this situation.

You asked about the difference between DBCC CheckDB ('', REPAIR_ALLOW_DATA_LOSS)? and ALTER DATABASE REBUILD LOG ON (NAME=,FILENAME='')

The thing is that you can run both to rebuild the log file, but with checkdb you rebuild the log and check database for integrity errors as well.

Also the SECOND with Alter database will not work if there where active transactions (not written to disk) when the Log file was lost. At startup or attach, the SQL will want to make recovery (roollback or rollforward) from the Log file which is not there. It happens when a disk crash or unexpected shutdown of a server occurs and the database is not cleanly shutdown. I guess it was not your case and all sorted out good for you.

Answer 2

Yes, those are two different statements, each doing very different things.

How did you delete the transaction log? Did you suffer a disk failure? Did you shutdown SQL Server and accidentally delete the log file?

Depending on the state of the database when the file was deleted, you may be able to get up and running by attaching the database and rebuilding the log by using:

EXEC sp_attach_single_file_db 'dbname here', 'file path and name here'

You can also see this blog post by Paul Randal: http://sqlhera.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx

Answer 3

What I've done was run the following scripts and it worked.

ALTER DATABASE TestDB2 REBUILD LOG ON (NAME=TestDB2_Log, FILENAME='D:\SQLLog\TestDB2_Log.ldf') 

dbcc checkdb (testdb2) 

ALTER DATABASE testdb2 SET MULTI_USER

Not sure though if this is the best way to do it. Would appreciate it if you can advise further...