SQL Server Transaction Log File Corruption

Problem

As a database administrator one of your key roles is dealing with data corruption. In this article we will learn how to solve Transaction Log file corruption. File corruption may happen because of multiple reasons, such as: –

  • Hardware issues with I/O subsystem for databases’ files.
  • Improper shutdown for databases.
  • A virus, malware or malicious software that make the files inaccessible.
  • No sufficient free space for file grow or exceeding the configured maximum size.

Solution

The first action to do, once you recognized the database is not accessible, is to check the SQL Server error log and windows event logs. For any hardware defects you may contact system administrators to fix the issue.To rebuild the corrupted Transaction Log file, we should put the database in the emergency state followed by single user mode: –

USE master
GO
ALTER DATABASE [TestDB] SET EMERGENCY
GO
ALTER DATABASE [TestDB] SET SINGLE_USER
GO

[info]Do not detach the corrupted database as it may not be able to attach again[/info]In first step we try DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command to fix it.

DBCC CHECKDB ([TestDB], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Above script may resolve the corruption. If this script does not solve corruption, you may try to run the following steps to bring the database up.

First you need to offline the database to be able to change database files: –

USE master
GO
ALTER DATABASE [TestDB] SET OFFLINE
GO

 

Browse to the Transaction log File path and change its name.

The following script will rebuild new transaction log file for the database: –

ALTER DATABASE [TestDB]
       REBUILD LOG ON
       (
              NAME= logicalname,
              FILENAME='F:MSSQLLOGTestDB_log.ldf'
       )
GO

After the SQL Server Engine rebuilt the Transaction Log File you need to bring the database online: –

USE master
GO
ALTER DATABASE [TestDB] SET ONLINE
GO

Then, you need to run DBCC CHECKDB to validate files’ consistency: –

DBCC CHECKDB ([TestDB])

If there is no consistency issue, you should return the database back to MULTI_User mode: –

ALTER DATABASE [TestDB] SET MULTI_USER
GO

Conclution

Bear in mind, by using this strategy you might lose some data that are written to the Transaction Log file but not hardened to the disk. So, the best approach is restoring the proper backup. It is very important to have an appropriate backup strategy to recover data in case of data corruption.

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

guest
0 Comments
Inline Feedbacks
View all comments

About The Author

Search Articles

Categories

Follow Fard Solutions