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.
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
[/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: –
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
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.