June 11, 2009 at 8:51 am
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
June 11, 2009 at 9:23 am
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.
June 11, 2009 at 10:14 am
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
June 16, 2009 at 4:50 am
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?
June 16, 2009 at 8:21 am
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
June 16, 2009 at 9:00 am
Randal, thank you for your reply I'm going to read right now your Technet article.
October 28, 2009 at 1:15 am
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.
October 28, 2009 at 12:27 pm
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
October 29, 2009 at 8:48 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply