SQL SERVER – FIX: Backup Detected Log Corruption in database MyDB. Context is Bad Middle Sector

During my consulting, I guide my clients to have a backup plan in place. One of my clients reported that whenever they try to take a Full Backup of a database, it fails precisely after 60% completion. Here is the error message while making database backup using SQL Server Management Studio. Let us fix error related to the bad middle sector in this blog.

System.Data.SqlClient.SqlError: BACKUP detected corruption in the database log. Check the errorlog for more information.  (Microsoft.SqlServer.Smo)

The above error gave us a hint that it is a corruption in transaction log file. As usual, I asked for ERRORLOG to check what we have there during the failure.

2018-03-07 13:25:40.570 spid62       Backup detected log corruption in database MyDB. Context is Bad Middle Sector. LogFile: 2 ‘D:\Data\MyDB_log.ldf’ VLF SeqNo: x280d VLFBase: x10df10000 LogBlockOffset: x10efa1000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x280d LogBlock.StartLsn.
2018-03-07 13:25:40.650 Backup       Error: 3041, Severity: 16, State: 1.
2018-03-07 13:25:40.650 Backup       BACKUP failed to complete the command BACKUP DATABASE MyDB. Check the backup application log for detailed messages

When I checked on the internet, most of the issue reported had a single solution – “Rebuild Transaction Log”. But as you can imagine, it is a risky operation concerning data loss. Then, something struck my mind to check VLF related info, and it was an interesting finding.

SQL SERVER - FIX: Backup Detected Log Corruption in database MyDB. Context is Bad Middle Sector error

In the above error, we do see the VLF SeqNo x280d, and it looks like a hexadecimal number. So, I converted it to decimal and got 10253. I ran the command “dbcc loginfo” on this database and could see this VLF SeqNo and the status was 2 which means active VLF. Then I tried to check if there was an active transaction using DBCC Opentran and output was as follows:

Transaction information for database ‘MyDB’.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (9877:25:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I checked what the transaction log waiting for is and we found the below info.

SELECT log_reuse_wait
,log_reuse_wait_desc 
FROM sys.databases
WHERE database_id = DB_ID('MyDB')-- replace DB Name

Below is the output

log_reuse_wait Log_reuse_wait_desc
————– ———————-
6              REPLICATION

WORKAROUND/SOLUTION

Maybe this is a special situation where my client had no clue about using replication on this database. This could be another classic scenario where replication related metadata was not completed removed from this database. I used the below command to remove replication from this database,

sp_removedbreplication 'MyDB'

After this, we did not see any old running replicated transactions (DBCC OPENTRAN was clean). VLF was in status 0 which means inactive. Interestingly, we were also successful in taking a backup of this database, and it was a happy ending!

Have you seen such issue earlier?

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Mirroring Error 1456 – The ALTER DATABASE Command Could not be Sent to the Remote Server Instance
Next Post
SQL SERVER – PowerShell Script – Delete Old Backup Files in SQL Express

Related Posts

3 Comments. Leave new

  • hi, that’s a good post.
    I have a similar issue, need help in sorting this as the MS case says its a Storage Issue just due to the keyword “bad sector”. Here is the error message
    Backup detected log corruption in database MYDB. Context is Bad End Sector. LogFile: 2 ‘E:\DatabaseFiles\WSS_Content_SIMS_M201812_log.ldf’ VLF SeqNo: x4ff VLFBase: x52759e0000 LogBlockOffset: x52df17d800 SectorStatus: 4 LogBlock.StartLsn.SeqNo: x4ff LogBlock.StartLsn.Blk: x34bc75 Size: xf000 PrevSize: xd400

    the VLF seqno shows 2 and is active, however there is no active connections.

    Reply
  • I have a similar issue. I see the same error log whenever I backup the transaction log.
    So, I can’t process the transaction backup.
    Now there isn’t any backup now.

    When I checked what the transaction log waiting for is and I found the below info.

    log_reuse_wait Log_reuse_wait_desc
    ————– ———————-
    2 LOG_BACKUP

    Could you tell me what can be a solution?

    Reply

Leave a Reply

Menu