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

[codesyntax lang="tsql"]
USE master
GO
ALTER DATABASE [TestDB] SET EMERGENCY
GO
ALTER DATABASE [TestDB] SET SINGLE_USER
GO
[/codesyntax]

[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.

[codesyntax lang="tsql"]
DBCC CHECKDB ([TestDB], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
[/codesyntax]
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: –

[codesyntax lang=”tsql”]

USE master
GO
ALTER DATABASE [TestDB] SET OFFLINE
GO

[/codesyntax]

 

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

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

[codesyntax lang=”tsql”]

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

[/codesyntax]

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

[codesyntax lang=”tsql”]

USE master
GO
ALTER DATABASE [TestDB] SET ONLINE
GO

[/codesyntax]

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

[codesyntax lang=”tsql”]

DBCC CHECKDB ([TestDB])

[/codesyntax]

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

[codesyntax lang=”tsql”]

ALTER DATABASE [TestDB] SET MULTI_USER
GO

[/codesyntax]

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

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan