Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
+1 for deleting the transaction log. :) Pleeeease tell us how that happened... –  KristoferA Jan 25 '11 at 5:03
    
Paul Randal is the best resource in this field of recovery. Look at his article: blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/15/… –  Marian Jan 25 '11 at 9:04
3  
Let's just say that we've all learnt the importance of database backups (and granting project managers access to the server ...) –  Fuzzy Purple Monkey Jan 25 '11 at 9:21

3 Answers 3

up vote 9 down vote accepted

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.

share|improve this answer
    
Thanks yrushka, this was the info that I was looking for. So is ALTER DATABASE REBUILD LOG ON + DBCC CHECKDB = DBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)? –  Fuzzy Purple Monkey Jan 27 '11 at 0:49
    
DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) run on database with emergency status does the following: –  yrushka Jan 27 '11 at 9:40
    
1. DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) run on database with emergency status checks the database for inconsistency errors, tries first to use the Log file to recover the database's inconsistencies. If this is missing, the transaction log is rebuilt. 2. Alter DATABASE REBUILD LOG ON - Is an undocumented procedure and yes requires a dbcc checkdb to fix the errors if any. –  yrushka Jan 27 '11 at 9:50

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

share|improve this answer
    
Thanks, SQL Server was shut down and the log file was deleted. The database spans several files - will that command still work? –  Fuzzy Purple Monkey Jan 25 '11 at 5:56
    
And can you describe the differences between those two commands? –  Fuzzy Purple Monkey Jan 25 '11 at 5:56
    
sp_attach_single_file_db does the same thing as CREATE DATABASE ... FOR ATTACH (all it does is some basic checking then uses the CREATE DATABASE with the FOR ATTACH switch). This is used to bring a file which isn't attached into the database and create a new log file at the same time. The ALTER DATABASE ... REBUILD LOG command takes a database which is already attached (but without the log file) and creates a new transaction log file. –  mrdenny Jan 26 '11 at 9:09

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...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.