An Effective Solution to Resolve SQL Database Corruption

I am a SQL Administrator and today I will share my experience about an issue on SQL server, which corrupted the transaction logs and left the database in a RECOVERY_PENDING state. Thankfully, I was able to resolve it.  

The SQL server experienced a faulty hardware issue and a faulty memory and it suffered a corruption in transaction log leading to unclean server shutdown. When the system was eventually online, it had a database which was not available in a usable state.

The transaction log is used by SQL at all times to ensure the consistency of the database. It provides SQL database the required consistency and durability which are necessary for any relational database engine. If the SQL service is stopped or the SQL database gets detached, it is the job of SQL server to shut down in a clean state, meaning it should run a checkpoint and write all dirty data pages on a disk. Further, the checkpoint also needs to make an entry into the logs that it has completed the task of running. If there is not enough space in the log, the checkpoint cannot run and SQL database cannot shut down cleanly.

When the database was attached and the service was restarted, SQL tried to restart-recovery on the database. Here the transaction log was used to bring the SQL database to a consistent transactional state. So far so good, but here the transaction logs were not available. And hence, the database could not be brought online, and based upon the failure stage, the database was marked in Suspect or Recovery Pending stage. 

The following sequence of activities were performed:

CREATE DATABASE AbcXyz

ON (NAME = ABC_dat,

FILENAME = ‘D:\Develop\Databases\ AbcXyz.mdf’,

SIZE = 10

MAXSIZE = UNLIMITED

FILEGROWTH = 5

LOG ON (NAME =  Abc_log,

FILENAME = ‘‘D:\Develop\Databases\ AbcXyz.ldf’,
SIZE = 5MB,
MAXSIZE = 100MB);
GO

ALTER DATABASE AbcXyz SET RECOVERY FULL;
GO

USE AbcXyz
GO

CREATE TABLE Filler (
ID INT IDENTITY PRIMARY KEY,
FillerStr CHAR(300)
)
GO

BACKUP DATABASE AbcXyz TO DISK = ‘D:\Develop\Databases\Backups\AbcXyz.bak’[/source]

Right, so that’s the setup all done. Now to fill the log up.

[source:sql]DECLARE @i INT;
SET @i = 0;

WHILE (@i < 10000)

BEGIN
INSERT INTO Filler (FillerStr) VALUES (CAST(@i AS VARCHAR(10)))
SET @i = @i+1;
END
GO

After just a 3779 inserts, an error message popped up:

Msg 9002, Level 17, State 2, Line 6
The transaction log for database ‘AbcXyz’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

At this stage, stop the SQL service and delete/rename the log file (D:\Develop\Databases\ AbcXyz.ldf). Now restart the SQL service. When I tried to access the database with the following query,

select MAX(ID) from AbcXyz.dbo.Filler[/source]

SQL server throws below error message.

Msg 945, Level 14, State 2, Line 1
Database ‘AbcXyz’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server error log for details.

With this error message, I came to the conclusion that the state of the database, as per sys.databases is in RECOVERY_PENDING state. ‘Restart Recovery’ cannot run due to the missing log file and the error log enumerates the following:

Error: 17207, Severity: 16, State: 1.

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘D:\Develop\Databases\AbcXyz.ldf’. Diagnose and correct the operating system error, and retry the operation.

File activation failure. The physical file name “D:\Develop\Databases\AbcXyz.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.

Now, the first question for me was “What is the SLA for the database server and how much data can the user afford to lose?

And the answer was as expected: “the database needs to be up and running a.s.a.p., and we can’t afford to lose data”.

Methods applied to resolve MS SQL corruption

There are two ways to fix RECOVERY_PENDING issue – restore from backup, or repair and rebuild logs. Further, there are two methods for log repair and rebuild – the DBCC CHECKDB command using the REPAIR_ALLOW_DATA_LOSS option and the software-based method. 

First, I wanted to check the health of SQL database backup to restore the database from backup. This is the easiest and most feasible options, provided the backup is in a consistent state.

• Restore MS SQL database from backup

The most feasible option to fix the database Transaction Log file corruption issue was to restore the database from the latest restorable backup and it had to be done from the ‘Full’ backup. The backup was available but it was not updated. This means, if we tried to restore from the available backup, we would be losing around last few hours data.

We opted for manual backup restoration process with the help of SQL Server Management Studio (available for free from Microsoft).

Note:

Process to restore MS SQL database from backup varies from version to version. Here our database was on SQL Server 2012.

We installed and connected the database to SQL Server Management Studio through Port 1433.

Database users could not afford such a huge data loss so the option of restoring MS SQL database through last available backup was completely ruled out unless there was no other option.

• Repair database with DBCC

I needed to run a repair to bring the database in RECOVERY_PENDING state to an online state. The repair utility will fix the structural problems, as caused by the missing log and bring back the database to a normal online state. 

The following command was used:

ALTER DATABASE AbcXyz SET SINGLE_USER

DBCC CHECKDB(AbcXyz, REPAIR_ALLOW_DATA_LOSS)

After running this command, I found that the CheckDB complained about the missing log and started the process of log-rebuild with the following warnings:

File activation failure. The physical file name “D:\Develop\Databases\AbcXyz.ldf” may be incorrect.

The log cannot be rebuilt because the database was not cleanly shut down.

Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.

Again, running the repair command did not resolve the issue. As there was not enough space in the log for a clean shutdown, running a checkpoint also prompted an error message stating that the database could not be repaired and the log can’t be rebuilt because the database was not cleanly shutdown.

Now this was a catch-22 situation for me. I couldn’t restore the database because the backup was not full and I couldn’t repair the logs because transaction logs were missing. The recommended approach is to resolve the error with an MS SQL database repair software. But first, I need to search for such software which could repair the database and make it available for users within no time.

• Repair with MS SQL Repair software

I did thorough online research for available software and decided to go with Stellar Repair for MS SQL. There were many reasons to support my selection-Along with the corrupted ndf and ldf file, the software also repaired corrupted log files. The software claimed to correct the bit-pattern and bring the database to a consistent state. Both these features were required to bring our MS SQL database online.

I also read a few case studies where the clients were happy in buying the software as they could easily repair the corrupted database and recovered the entire data, which they otherwise thought to be lost. Here’s the link.

Again, before investing in the software, I called up the customer support to know the procedure of database repair and what’s the probability of getting back the data. They informed me that I could download and test the software and the preview would show the recoverable database. That’s what I actually wanted.

I installed the software and submitted the file. To my surprise, I could preview the entire SQL database on my screen. Without wasting any more time, I activated the software. There also, the support team helped me in getting the activation key without any time lag. And, I could save the entire database as a new database. The new database was accessible and did not have any corrupt elements.

I breathed a sigh of relief!

I wished I had thought of this option before everything else, then the downtime would have been much lesser.

Conclusion

As MS SQL Administrators, it is our job to keep the database up and running. And if there is an issue, then we should resolve the issue at the earliest to keep the downtime to the minimum and recover the database in entirety. Hence, this is for us to decide which protocol, backup restore, repair with DBCC command or repair with third-party the software delivers the maximum value in the given circumstances. There is no better solution than restoring the MS SQL database from a good backup and the next best option is to repair the database by using software such as Stellar Repair for MS SQL.

This crisis situation taught me that the database should be backed up consistently and also a software should be available as a help to overcome such crisis. Here’s a small but informative guide for database administrators.