The estimated reading time for this post is 2 minutes
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: –
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
In first step we try DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command to fix it.
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.DBCC CHECKDB ([TestDB], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS; GO
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:\MSSQL\LOG\TestDB_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
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.
Leave a Comment