2

I have a database which I have inherited which is just a collection of static data in seperate tables with no relations between tables. It is essentially just a number of tables which are the results of queries run against other databases. No-one else has access to the database. It turns out this hasn't been backed up.

A query was running over the weekend and something happened, perhaps a power cut. Now when I start SQL Server the database in question goes into recovery. Normally I would just leave this and let the recovery finish but in this case having left it a few hours I looked back and the estimated time to completion is 4.5 days!!!

Now, I'm not bothered about anything which happened as a result of the query which I ran over the weekend, I don't actually need the results of that anyhow now. All the query was doing was creating a new table, reading data from existing tables, no writing to existing tables. I can recreate almost everything in the database by running the original queries which will just take a few hours. My biggest problem is that I need access to the database, I can't wait 4.5 days!

Is there a way I can delete the transaction log, accepting that it won't recover whatever it was interruped over the weekend, so that SQL will stop trying to recover the database so I can carry on using the data within it?

2

I wouldn't delete it, just in case you run into problems. If space isn't an issue create a copy of the original files with the instance offline, rename the original (so they aren't found on restart) then try:

CREATE DATABASE MyDatabase 
ON (FILENAME = [mdf path]) FOR ATTACH_REBUILD_LOG 
  • Thanks that's really useful. One of my colleagues also mentioned another possible solution which is to set the database into EMERGENCY mode and use DBCC CHECKDB (database_name, REPAIR_ALLOW_DATA_LOSS) which also seems to work and was quick – Steve_m Dec 3 '13 at 9:38
  • That works, however since you mentioned that you don't have a backup, this operation is on your original database. Mark's suggestion is saver in a sence that you are working on a copy. – Edward Dortland Dec 3 '13 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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