Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup detected log corruption in database


Backup detected log corruption in database

Author
Message
DBN
DBN
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
I there!

I am trying to reduce my log file and I used the following steps:

USE suplies
GO
CHECKPOINT
GO
BACKUP LOG suplies TO DISK = 'D:\backup_log.log'
GO
DBCC SHRINKFILE (suplies_log, 1);
Go



But SQL Server is returning the following error:

2009-06-11 15:11:47.93 spid63      Backup detected log corruption in database suplies. Context is FirstSector. LogFile: 2 'C:\MSSQL.1\MSSQL\Data\suplies_log.ldf' VLF SeqNo: x2c77c6 VLFBase: x157b9a000 LogBlockOffset: x157bd1e00 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x0 LogBlock.StartLsn.Blk: x0 Size: x0 PrevSize: x0

2009-06-11 15:11:47.98 Backup Error: 3041, Severity: 16, State: 1.




I always used this procedure to reduce the log file's size but now I'm getting this error. I'm working on a production server so any solutions provided should be carefully applied.

Thanks in advance
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36344 Visits: 18752
I haven't seen log corruption before.

Can you make a log backup directly to a new file name?

If not, can you run a full backup? If you can, try BACKUP LOG WITH TRUNCATE ONLY, to clear the log, run a full backup, and then try a new log backup.

If you have corruption, it's usually a hardware issue somewhere. I would contact Microsoft PSS to work through this.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47415 Visits: 44405
The following should work, but take a backup first (as with all production-type fixes)

Switch the database to simple recovery.
Run a checkpoint
Switch back to full recovery
Take a full backup
Test log backups to see if they work.

If so, take the shrink out of the log backup job. It's not a good idea. Recommendation with the log is to set it to the size it needs to be based on the frequency of your log backups and the activity of the database and then leave it alone.

Check windows event logs and any hardware logs. Corruption's usually a problem with the IO subsystem.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


DBN
DBN
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
Thank you for all feed-back.

Actually this happened after the server was shut-down because of a power failure and the database entered to "In Recovery" mode after a long time the database was on-line and everything was OK.
Last friday I ran the command again and the problem has gone...

Now I would like to know what I should to do keep my transaction log tiny because after I ran the command above I've shrinked the log file from about 20GB to only 15 MB (yes... Megabytes). I have my database mirroring to another server and I really need to have the transaction log more small because with this size I have to wait more than up to three hour to bring on-line a database. What maintenance I should do in the databases to keep the transition from a Mirror to on-line more smooth and quick?
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 1714
See my blog Importance of proper transaction log size management

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
DBN
DBN
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
Randal, thank you for your reply I'm going to read right now your Technet article.
sejal p gudhka
sejal p gudhka
SSC-Enthusiastic
SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)SSC-Enthusiastic (142 reputation)

Group: General Forum Members
Points: 142 Visits: 236
I too faced similar situation where in taking log backup to disk throws error message as BACKUP detected corruption in the database log. Following are the things that I have done and it worked fine for me

1) Took full back of database
2) Run checkdb on database
3) Backup log with truncate_only options
4) Took again full back of database
5) Run backup log to disk and it work working fine.

I guess this is due to some VLogs problem.
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2171 Visits: 1714
No, nothing to do with having too many VLFs or the size of your transaction log - it's your I/O subsystem. You just threw away the log and made it smaller so the corrupt portion isn't part of the active log any more. It'll most likely happen again if you don't take corrective action - run I/O subsystem diagnostics, check firmware levels, etc

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
Jeff W
Jeff W
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 51
I agree with Paul's assessment about it being the I/O subsystem. One thing I would not do in this situation is run a full backup. Chances are it is not recoverable.


"Keep Your Stick On the Ice" ..Red Green
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search