Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup detected log corruption in database Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 7:33 AM
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
Post #733084
Posted Thursday, June 11, 2009 9:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:18 PM
Points: 33,155, Visits: 15,289
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
Post #733129
Posted Thursday, June 11, 2009 10:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 42,756, Visits: 35,849
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 2008, MVP
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

Post #733185
Posted Tuesday, June 16, 2009 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 7:33 AM
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?
Post #735546
Posted Tuesday, June 16, 2009 8:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #735701
Posted Tuesday, June 16, 2009 9:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 7:33 AM
Points: 3, Visits: 7
Randal, thank you for your reply I'm going to read right now your Technet article.
Post #735745
Posted Wednesday, October 28, 2009 1:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 17, 2012 11:29 PM
Points: 138, 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.
Post #809729
Posted Wednesday, October 28, 2009 12:27 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #810217
Posted Thursday, October 29, 2009 8:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 8, 2013 3:39 PM
Points: 349, Visits: 49
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
Post #810828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse