How SQL Server Repair Corrupted & Deleted Database with Transaction Log File
In this write-up, we will describe about how to repair a SQL Database which has been deleted from corrupted SQL Server Transaction Log File. Therefore, if you are searching for SQL Server Corruption recovery with SQL database transaction Log file then, it is right platform for you to solve this task simply.
Error Definition
SQL Server device reads the complete transaction log file and execute recovery process which has redo & undo both phases, when start SQL Server Service. If recovery or reading processing fails then the database is not be brought online & marked as Recovery Pending or Suspect, based on the breakdown stage. There may be lots of reasons for SQL transaction Log file corruption, which includes –
- The system shut down suddenly without proper termination of SQL database.
- Due to virus, malware attack & malicious apps which corrupted or damaged files & make it unreadable or inaccessible.
- Configuration & hardware problems had risen with I/O subsystem which is handled tohost user database & system files.
- SQL Transaction Log file had exceeded the configured file size and ran out of free space.
Troubleshooting
If users are not capable to carry the database online as it fixed in SUSPECT or recovery pending statement, firstly; need to execute is reconsidering SQL Server Error Logs & Windows App and System event logs on SQL Server that is providing this database. If you detect hardware issue, go to system administrator or hardware vendor to repair the issue. If the error is due to transaction Log file corruption then continue to read the write-up and explore that how to fix this issue.
There are lots of error which indicates proper issue with SQL Server Transaction Log file, such as –
- SQL Server noticed a logical consistency-supported I/O issue: incorrect checksum (actual: 0x186b2635;expected: 0x186ba635). It raised during a read of page(2:0) in database ID 22 at offset 0000000000000000 in file ‘C:\Program Files\Microsoft SQLServer\MSSQL13.MSSQLSERVER\MSSQL\DATA\XXX.ldf’.other messages in SQL Server issue log & system event log offers more details. It is a harsh issue situation which intimidates database components and may be corrected immediately. Check (CHECKDB DBCC) a full database consistency completely. This error may be due to multiple factors, for more information, look SQL Server books online.
- The log may not be repaired because there was an open transaction or users when database wasclossed, no verified point raised to the database and it was read-only. This error can rise if transaction log file was manually lost & deleted due to environment & hardwarefailure.
- An activation file error occurred which physical file name “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\XXX.ldf” can be inaccurate. Correct & diagnose additional errors & retry the operation.
The safest & best function to repair database transaction Log file corruption error is saving database from the latest backup chain, which has restoring the full backup. All transaction Log Backup & differential backup to the last strong point in time before the corruption raised.
But what if this function is not appropriate ; because of having no proper backup strategy configured & some of backup files in latest backup chain had lost? In this situation, users cannot admit the data loss which is outcome from saving the last backup file or half backup chain before reaching lost backup database file as it contains critical data. The final application function deserves trying is repairing the transaction Log file, which we see in next section, bearing lost of recovering, redo & undo transactions which were situated in the original transaction Log file.
Resolution
For SQL Server repair corrupted database transaction log file, we must put the database in emergency state with a user mode, by entering the below command –
Using this way, we make sure that SQL database is brought up without the transaction Log file which enable to run the proper commands which are necessary to repair the corruption error.
Now try to execute the DBBC CHECKDB command by the REPAIR_ALLOW_DATA_LOSS function to verify the database for inconsistency issue and apply few special repairs to recover Transaction Log corruption error, as in the T-SQL script –
Regrettably, the SQL Transaction Log corruption database cannot repair, by DBCC CHECKDB command & necessary further troubleshooting to fix it. The error message obtained from DBCC CHECKDB command in this case will be –
After reaching the step, you still got SQL Server Transaction Log File corruption error? We recommend that you don’t detach the database & try to attach it without referencing the transaction Log file in order to generate a new Transaction Log file
If you would like to detach corrupted database, as provided –
After that, attach it while deleting the reference to SQL Transaction Log file
Attach process fails and displaying error message below –
Sequentially to attach it correctly without using SQL Server Transaction Log file, rename the previous transaction Log file –
Now run the CREATE DATABASE command by FOR ATTACH_REBUILD_LOG command as provided below –
SQL database will be attached by the MDF file along with a new SQL transaction Log file generated on the database to replace the corrupted one, as provided below –
Another function to rebuild the SQL Server Transaction Log File (corrupted) is the database offline as provided below –
The SQL database marks as offline as provided below –
After that modify corrupted SQL Transaction Log file name, as displayed below –
Now run ALTER DATABASE T-SQL command below, by REBUID LOG function, & provide the SQL Server Transaction Log file original name in order to repair the file again for that database, as in the T-SQL script provided –
The SQL Server device will rebuild the Transaction Log File with warning message, recommended to run the DBCC CHECKDB command to confirm the physical database consistency, as displaying below –
Now, the SQL database will be online as displaying below –
SQL database is now online under a user mode state, as displaying below –
Now users can return SQL database back to MULTI_USER online mode, by ALTER DATABASE statement below –
The SQL database will now be in online state, as displayed from the SQL Server Management Studio –
As provided in previous warning message, we will run DBCC CHECKDB command, in order to verify the physical database consistency after rebuilding the Transaction Log file.
The DBCC CHECKDB command will display that there is no data consistency error on the database required to be repaired, as the output message shown –
SQL Server corruption with Transaction Log File recovery has been fixed now.
Even though, the provided method supports fixing of SQL Server recover corrupted database with transaction Log file error, it may resultin losing some data which are written to transaction Log but not threatening to the original disk after CHECKPOINT process without knowing the exact lost data. Nothing can be better than having an exact proper SQL Database recovery strategy that assists to repair the data in case of corruption without failing and data loss risk.
