We have a very large database (~6TB), whose transaction log file was deleted (while SQL Server was shut down. We have tried:
- Detaching and reattaching the database; and
- 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.
Is there a difference between the command above and the following one?
DBCC CHECKDB ('<dbname>', REPAIR_ALLOW_DATA_LOSS)
Should we be executing
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.
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. Let's just say that we've all learnt the importance of database backups (and granting project managers access to the server...).