Sql-server – Error 23 on transaction log backup, full backup works fine

backupcorruptionsql serversql-server-2012transaction-log

I have a database with the database files on the E: drive, and logs files on the D: drive.

I'm getting the following error when trying to run a transaction log backup:

Read on "D:\LOG\AOA_Log.ldf" failed: 23(Data error (cyclic redundancy check).)

I'm also getting a bad block message in the Windows event log.

A Full database backup works fine.

Can I change the database property to simple restore and shrink the log file?

Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

The server is a VMWare guest with the data residing in a disk array.

Best Answer

To answer the two specific questions in your post:

Can I change the database property to simple restore and shrink the log file?

You can switch to the simple recovery model and shrink the log. This might workaround the problem for a little while if it moves the data off of the faulty area. Or it might cause more problems (if there are more "bad" areas in this drive / array that get written to).

Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

Again you can do that, but there are some caveats. Tony mentioned one in a comment:

You will need to stop SQL Server or detach all databases that have files on D: before you dismount it.

So there will be downtime involved for this. Tony also mentioned that, before disconnecting everything, you should take a full backup and restore it somewhere else, to make sure the backup was good. That way, once you have finished chkdsk and resolved any issues there, you can confidently restore from that backup.

Related Solutions

Sql-server – What ‘resets’ the transaction log

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

Sql-server – Shrink Transaction Log While Using AlwaysOn Availability Group

In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info.

Do not shrink to 160MB. Determine why did the log grow to 121Gb so it does not repeat (you have a suspicion, would be nice to confirm if possible). Size the log to a size appropriate for your operational needs. Log growth is a serious problem, it cannot use instant file initialization and all your database activity will freeze while the log grows and is being 0-initialized. Users and apps hate it when it occurs. If you understand the impact and your users are OK, you can shrink once to a small amount (160MB is probably too small though) and let it grow until it stabilizes.

Best Answer

Related Solutions

Sql-server – What ‘resets’ the transaction log

Sql-server – Shrink Transaction Log While Using AlwaysOn Availability Group

Related Question