May 14, 2009 at 2:21 pm
Hello,
Does anyone know how to recover a ldf from this error?
Error: log cannot be rebuilt because the database was not cleanly shut down
Thank you in advance....
May 14, 2009 at 2:27 pm
Only thing I can think of is restore to the last good point. That's probably your last backup.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2009 at 2:30 pm
Thank you for your response..
May 14, 2009 at 2:43 pm
Why are you rebuilding the log? What happened to the existing log file?
Two options.
1) Restore from last backup. Recommended
2) Hack the DB back into the server, do an emergency mode repair then rebuild the log. Risk of transactionally inconsistent data. Really only for when there is no backup. See - http://www.sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2009 at 2:53 pm
The customer did not not have a good backup...they thought they did.
They are trying to restore the log to rebulid the db from the transactions.
Thank you for your post...
May 14, 2009 at 2:59 pm
mikeandanne (5/14/2009)
They are trying to restore the log to rebulid the db from the transactions.
Can you elaborate?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2009 at 4:18 pm
though your explanation of the issue is bit vague.
If you want to restore the database and wants to bring the DB online
use the CREATE DATABASE
and with force attach thats the keyword.
WARNING --- THIS IS NOT A SUPPORTED AND THE OUTPUT OF IT CAN BE VERY UNDESIRABLE
If you can explain the issue , it will help
May 14, 2009 at 8:35 pm
Thank you to all for your posts...
May 15, 2009 at 1:42 pm
I think I might be the client in question on this post and I'm not a SQL programmer or system admin, hence the newbie mistakes.
Here is what happened:
1. The log file filled up all the space available on the disk.
2. I took a backup of the log file and then tried to shrink it - no response.
3. I believe something I read on the internet (stupid move 1), that if the log file was removed it would be recreated automatically.
4. I took the database offline and started moving the log file but it was going to take 15 mins. Hence, since I had a backup I just deleted it (stupid move 2).
5. Problem as described
6. I discovered that my IT support had not checked the backups and they had not been working.
7. Ensuing horror.
So I DO have a backup of the log file only but I can't restore it because of the 'not properly shut down' error.
I have now been running DBCC CHECKDB (, REPAIR_ALLOW_DATA_LOSS) for 24hrs and it still has not completed (70GB of data). I don't know if it is doing anything at all because the disk IO and CPU usage are both very low.
So, should I stop the checkdb and try a manual fix? If so, which one?
May 15, 2009 at 1:53 pm
splatt (5/15/2009)
So, should I stop the checkdb and try a manual fix? If so, which one?
Do you by any chance have the last full backup that was made? If the log backup succeeded, there has to be a full backup that it was based on. If we can find that, then this mess can be recovered by restoring full and log backups.
What steps, precisely, have you taken so far?
Edit: If this is urgent, you are far better off contacting MS's Customer Support people. They will charge, but they can walk you through the fixes step by step and you won't have to wait hours or days for someone on a forum to reply.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2009 at 3:08 pm
No, as mentioned a few times now, there is no full backup. I ran this command:
BACKUP LOG database TO DISK='F:\Backup\database_Log.bak'
May 15, 2009 at 3:21 pm
If that operation succeeded, there is a full backup somewhere. If there was no backup at all, the log backup would have failed with an error like "No current backup exists"
Whether you can find that backup or not is another matter.
What are the exact steps you've done up until now to get the DB back?
p.s. I'm going to bed shortly. Someone else might pick this up, if not, I'll check again tomorrow morning.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2009 at 3:32 pm
Thanks, Gail
Here are the recovery steps so far:
1. Created a new database with exactly the same name and file names as the problem database.
2. Detached that database, deleted its log file. Replaced its database file with the problem database.
3. Attached with a special command (can't remember exact syntax), something like 'ignore filename'.
4. Set it into emergency mode, set single user access. Database had read-only access - could see tables and stored procedures.
5. Started running CHECKDB repair operation.
May 15, 2009 at 3:38 pm
Ok, in that case you have to let the checkDB finish. There is no manual steps that you can take, you're already doing them.
See the link that I posted earlier in this thread, to Paul Randal's blog if you get stuck. In that post he did a walkthrough of this problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2009 at 3:47 pm
Repair could take a long time depending on what corruption has been found, and a host of other factors. See CHECKDB From Every Angle: How long will CHECKDB take to run?.
You should be able to get an idea of what it's doing. If you select * from sys.dm_exec_requests and find the row for DBCC, what are the values of the percent_complete and command columns?
Also, are there any messages in the errorlog from DBCC? E.g. 5268?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply