Corrupted log file - SQL Server 2005 Express

Question

Post reply

Corrupted log file - SQL Server 2005 Express

lior-758738

SSC Enthusiast

Points: 178
More actions
February 17, 2009 at 1:55 pm

#201303

Hi,
A Message from the error log file:
Backup detected log corruption in database system. Context is FirstSector. LogFile: 2 'd:\zoom\MSSQL\MSSQL.1\MSSQL\DATA\system_log.LDF' VLF SeqNo: x2572 VLFBase: x4a60000 LogBlockOffset: x4ae3200 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x2043 LogBlock.StartLsn.Blk: x200000 Size: x0 PrevSize: x0
Any ideas ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please run the following and post the results.

DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS

Is that database a critical production DB that you're taking log backups of?

Are there any other errors in the log?

Do you have a clean backup?

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

lior-758738 SSC Enthusiast Points: 178 More actions · Answer 2

Hi

Thanks for the reply

I ran the command in Management Studio and the output I got was just "Command(s) completed successfully."

Is that what you asked me to do ?

Yes. It is a critical production database.

The error log file I am talking about is named ERRORLOG from LOG directory in SQL Server folder. It contains 4000 rows,

I don't think every row means error. I do see "Error: 3041, Severity: 16, State: 1." every 60 minutes, which is the backup schedule.

What do you mean by "clean backup" ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

lior (2/18/2009)
Hi
Thanks for the reply
I ran the command in Management Studio and the output I got was just "Command(s) completed successfully."
Is that what you asked me to do ?

Yup. That's good.

Yes. It is a critical production database.

Are you taking log backups or is it in simple recovery?

The error log file I am talking about is named ERRORLOG from LOG directory in SQL Server folder. It contains 4000 rows,
I don't think every row means error. I do see "Error: 3041, Severity: 16, State: 1." every 60 minutes, which is the backup schedule.

Yes, that's the one I'm talking about too. Are there any other error messages in there that look like they're related?

If so, post them. If you're not sure, zip and attach the entire file

What do you mean by "clean backup" ?

A backup that doesn't have the corruption in it.

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

lior-758738 SSC Enthusiast Points: 178 More actions · Answer 4

I am only taking backups.

When I run a backup from Management Studio everything go smooth.

The error message apears only when the application that is using the database tries to execute a backup.

The only way I know how to verify that a backup is clean is to actually restore from it. That means downtime and I don't wanna go there.

ERRORLOG Attached.

Thanks a lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

lior (2/18/2009)
When I run a backup from Management Studio everything go smooth.
The error message apears only when the application that is using the database tries to execute a backup.

So what's the difference between the way you backup from management studio and the way the app does a backup

The only way I know how to verify that a backup is clean is to actually restore from it. That means downtime and I don't wanna go there.

That is the only way to tell is a backup's clean if you don't know when the corruption started. Don't restore over the existing DB. Restore somewhere else and check.

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

lior-758738 SSC Enthusiast Points: 178 More actions · Answer 6

I don't know what is the difference. One assumption is that the app perform some checks on the database and if it find errors it doesn't actually execute the backup. Another assumption is that the backup ran by the app with different parameters than I do from MS (Right click on the databse > all tasks > Backup databse)

The resored database could be examined only with all other parts of this system including other databases, logins and files.

Is there a way for re-building log files ?

Paul Randal One Orange Chip Points: 29544 More actions · Answer 7

There's nothing the application can do to check the consistency of the transaction log - no such capability exists. If the error message occurs only when the application triggers a backup, it must be doing a different kind of backup than you've tried.

Can you please look in the application to see what it's doing? Or use profiler to trace what SQL statements it is using. Error 3041 is that backup failed - so whatever you're doing from SSMS isn't the same as the app's doing.

Thanks

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