Tlog backup failure

Question

Tlog backup failure

Anonymous

MyDatabase is set up in High Availability Group. The tlog backup was failed with the error. The MyDatabsase1_log.ldf file keep growing. How to troubleshoot this error and fix it? Backup detected log corruption in database MyDatabase. Context is Bad Middle Sector. LogFile: 2 'l:\log\MyDatabase1_log.ldf' VLF SeqNo: x5b9ab5 VLFBase: xcb8400000 LogBlockOffset: xce1b85000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x5b9ab5 LogBlock.StartLsn.Blk: x14bbf6 Size: xf000 PrevSize: xf000

Anonymous

2024-02-22T22:20:51.6733333+00:00

The SQL version is Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
LiHongMSFT-4306 31,621 Reputation points

2024-02-29T06:50:05.9533333+00:00

Hi @szh8866-8371 Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer accepted by question author

0 additional answers

Your answer

Anonymous

2024-02-22T22:20:51.6733333+00:00

The SQL version is Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
LiHongMSFT-4306 31,621 Reputation points

2024-02-29T06:50:05.9533333+00:00

Hi @szh8866-8371 Do you have further question on this issue, could we offer more support?

If any answer helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Answer 1

Erland Sommarskog 134.7K MVP Volunteer Moderator

Sounds like you are in big trouble. That is not good news at all.

First of all, corruption does not happen out of the blue, but it is always due to faulty hardware, typically the I/O subsystem. So you need to replace your hardware.

The next question is: how do you bring the database over to the new hardware? I would expect BACKUP DATABASE fail with the same or similar error, but you could try. If it succeeds, run DBCC CHECKDB after RESTORE.

Else you would need create a new database from scripts and copy data over with BCP, SSIS or similar.

Anonymous

2024-02-23T18:41:28.37+00:00

I removed the database from High Availability Group by suspend the movement and re-backup the tlog, it run successfully after that. the log file stop growing. How can convince the VM admin that the I/O is in trouble?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-02-23T18:52:54.3833333+00:00

The process of convincing other teams about the hardware issue can be a profressional challenge. If it happens agai, the best may be to open a case with Microsoft, to get the message from an source that is considered authoritative.

Also run DBCC CHECKDB and keep an sys on msdb.dbo.suspect_pages.
Anonymous

2024-02-23T18:56:19.4566667+00:00

I suggested the I/O issue before and was told that no issue can be detected. I like your idea. But how to open a case with Microsoft?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-02-23T18:58:23.8233333+00:00

I don't know what organization you are in, but if it is of any size, I would guess that you already have procedures for this.
Anonymous

2024-02-23T18:58:30.33+00:00

I run the DBCC CheckDB(MyDatabase). What do you mean by "keep an sys on msdb.dbo.suspect_pages"?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-02-23T19:01:39.08+00:00

Sorry, "sys" should have been "eye". That table should be empty.
Anonymous

2024-02-23T19:01:46.71+00:00

No one in our IT has opened a case with MS before. if you don't mind, could you show me how other company did with MS? I appreciate it.
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-02-23T19:05:16.85+00:00

Usually you have a support contract. I never had to open a support case myself, so I don't know the exact procedures.

Then again, if you don't have a support contract, it can be a bit expensive to have someone to tell you that your hardware is bad.
Anonymous

2024-02-23T19:10:44.5766667+00:00

Understand. Do you have information about Microsoft support Contract for SQL server?
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2024-02-23T22:34:10.6533333+00:00

As I said, I leave it to someone else in IT to open support cases and sign support contracts. Working as an independent consultant, it is nothing I need to do myself.
Anonymous

2024-02-23T23:00:03.3+00:00

Understand. Thanks for you help

Share via

Tlog backup failure

0 additional answers

Your answer