The estimated reading time for this post is 2 minutes

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: –

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

In first step we try DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command to fix it.

Source code
   
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: –

Source code

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: –

Source code

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

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

Source code

USE master
GO
ALTER DATABASE [TestDB] SET ONLINE
GO

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

Source code

DBCC CHECKDB ([TestDB])

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

Source code

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.

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2015. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website