6

Hi I am attempting to backup the transaction log file to setup mirroring in SQL Server

I execute

BACKUP LOG CUSTOMER TO DISK ='K:\JonDB\CUSTOMER.trn' WITH INIT
GO

And I receive

Processed 6587361 pages for database 'CUSTOMER', file 'CUSTOMER' on file 1.
Processed 0 pages for database 'CUSTOMER', file 'CUSTOMER_log' on file 1.
Processed 6 pages for database 'CUSTOMER', file 'CUSTOMER_log2' on file 1.
BACKUP DATABASE successfully processed 6587368 pages in 969.013 seconds (46.948 MB/sec).
Msg 3049, Level 16, State 1, Line 5
BACKUP detected corruption in the database log. Check the errorlog for more information.
Msg 3013, Level 16, State 1, Line 5
BACKUP LOG is terminating abnormally.

Where is the error log? I have a lot of disk space available so it cannot be this. I need to create the backup of the database and transaction log file to setup mirroring I believe. If I can get around this step, it would be ideal.

3

You appear to have corruption in your transaction log file. This needs to e resolved before you continue with you mirroring configuration.

Where is the error log?

Using SSMS the error log can be found in the management folder.

I would check the log for more information and run a full DBCC CHECKDB to ensure the database is OK.

Paul Randall is an expert in data corruption in SQL Server and he mentions a fix to your problem in this article. I would read this through to get a good understanding of the situation you are in. In brief he says that the situation may be solved by switching the database to SIMPLE recovery mode, back to FULL recovery mode and then performing a FULL backup. The full backup restarts the log chain.

I would also inspect the hard drives that the transaction log file is running on as they may have caused the corruption if they are faulty.

0
2

BACKUP detected corruption in the database log. Check the errorlog for more information.Msg 3013, Level 16, State 1, Line 5 BACKUP LOG is terminating abnormally.

The message is straight forward there is corruption in transaction log and since full backup also includes some amount of transaction log the backup failed. You might get lucky with backup by using continue_after_error clause.

BACKUP LOG CUSTOMER TO DISK ='K:\JonDB\CUSTOMER.trn' WITH INIT,continue_after_error
GO

NOTE: DBCC CHECKDB does not performs complete consistency check of transaction log it only checks the active portion of the log. The active portion of the log is checked and used as a by-product of creating a database snapshot to run the consistency checks on. There’s no consistency checking of the transaction log – only checksums that are checked as log records are read, for whatever reason.

To get with log corruption I suggest you read this SQLmag.com article. Paul has given reply to transaction log corruption. What he has suggested is

If a corrupt log record is encountered during a transaction log backup, the backup will fail – but that’s all. You can work around this by:

  1. Switching the database to the Simple recovery model

  2. Performing a checkpoint (which should clear the active log as long as nothing else requires the log to be kept active)

  3. Switching back to the Full recovery model

  4. Reestablishing the log backup chain by performing a full or differential backup

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy