Product Review – Stellar Repair for MS SQL Database

How Stellar Repair for MS SQL helped resolve the Recovery Pending issue

It is well stated, a small gap in database administration may be disastrous. That’s what happened with our organization’s MS SQL Server 2017 Enterprise edition. There was a sudden power outage on the premises. Thankfully, the SQL Server had power backup which insulated the database from collapsing. But, the monitoring server broke down due to a mistake of the monitoring team; it had missed out providing the power-backup to the monitoring server.

And that’s not all! Just when the monitoring application stopped, the SQL database suffered from the “Recovery Pending” issue. As the monitoring services had already stopped, it could not throw the alert when the SQL Server got affected due to the Recovery Pending issue.

Amongst all this turmoil, the database services came to a standstill.

Root Cause Analysis of the SQL Server Issue

I prepared a Root Cause Analysis Report of the events, which revealed the following:

At 2:30 hrs., CST, there was a sudden power shutdown due to which:

• Microsoft SQL database server monitoring services stopped
• Monitoring application stopped throwing monitoring alerts
• Database server suffered from “Recovery Pending” issue. As the monitoring application could not register and send the alert, the SQL administrator was not alerted in time
• Database Administrator was trying to bring back the monitoring server to the ‘up and running’ state and missed checking any unusual event on the SQL server.

The Root Cause Analysis (RCA) report also showed that during sudden power shutdown, the server switched over to the power backup mode, but it took a fraction of a second in the switch-over.

During that time an uncommitted query was in the active state resulting in deletion of that transaction log file. This deletion of Log file further resulted in recovery pending state, which prevented the recovery from getting initiated.

Despite setting up all the necessary precautions and measures to keep the SQL database up and running, it is quite uncertain when an instance of SQL database gets stuck, the situation has a direct impact on the performance and availability of the server. This was exactly the situation where the SQL database instance was stuck due to the server’s ‘Recovery Pending’ state. The error had also stopped the database recovery from getting initiated.

Restorable Backup came to the rescue

At the moment, the best probable solution was to restore SQL database from the available backup.

I had taken full care to install the backup of MS SQL database, apprehending such issues. To my utter disbelief, the backup was corrupted as well. Though backup was configured, it wasn’t possible to restore the database from this backup. Recovery pending issue led to the deletion of the transaction log file, further resulting in incomplete restoration.

Repair MS SQL Database through Repair_Allow_Data_Loss

When SQL database could not be restored through the available backup, then I resorted to perform database repair with the help of “REPAIR_ALLOW_DATA_LOSS”. Unfortunately, due to limitations of the Repair command, the process could not be completed.

Repairing the database with Repair_Allow_Data_Loss command may have resulted in loss of unrepaired data. Detaching and attaching the SQL database could not resolve the issue, as the last transaction log remained inaccessible and corrupt.

The issue was still unresolved. Further, none of the applied solutions worked.

Repaired MS SQL Database with Stellar Repair for MS SQL

I had evaluated the SQL repair utilities and downloaded the trial version of Stellar Repair for MS SQL. Next, I opened the SQL database repair software. At first instance, the software interface looked easy to work with.

I could comfortably browse and select the corrupted MS SQL database.

Then I used its Repair feature to check if the software repaired the available database. As a precaution, I had taken a backup of the available database.

I waited for some time for the software to repair the SQL database. To my surprise, it repaired the complete database including the last transaction, and displayed the complete database on the screen.

I found out that the software had recovered ROW and PAGE compressed data and also recovered deleted entries. In fact, the deleted transaction log file was also fully recovered.

However, it was an enormous database which did not allow me to verify, if the repaired database was complete. Hence, I clicked on the Log file available in the software to identify what all was recovered and noticed that it listed the details of Tables, Indexes, Keys, Rules, accurately.

Since I was now confident about the solution, I contacted Stellar Customer Support team to understand how to save the repaired SQL database, who helped me activate the software and save the database to a secure location.

The software resolved the Recovery Pending issue within a short period. Also, it maintained database integrity.

The SQL repair software supported MS SQL Server 2017 Enterprise edition thus making the recovery process possible and convenient.

Conclusion

Managing a constantly evolving database, generated by a team of almost 1000 resources is a critical task. Issues like database corruption, virus infection, and latency errors cause impediments in the smooth functioning of the database.

Considering these factors, our team of database administrators decided to configure an on-premises relational database management system which would help overcome database issues and ensure an uninterrupted data flow. After proper research of the available database systems, it was decided to configure MS SQL Server 2017 Enterprise edition.

This decision was backed by multiple factors- easy configuration of database app, established ANSI and ISO standards and that the database could host and manage multiple databases.

But, SQL configuration is not a standalone process. It has to be supported with managed services.

These managed services like monitoring, backup and maintenance by using reliable applications help in managing the server, the database and the related services. Along with the managed services, I had also ensured that the following precautionary measures were implemented for the SQL database:

• Incremental and full on-premises backup of SQL database
• Custom Cron jobs to help detect and repair anomalies in the database and ensure no lag in uptime
• Provision for optimization and regular updates for SQL server
• Monitoring application to check the logical and physical health of the database
• Routine maintenance of the hardware and software
• Power backup for graceful shutdown of the database

Installing the MS SQL database helped in regularizing the incoming and outgoing database requests. And installing adequate managed services ensured peace of mind that nothing could go wrong, as the server, its services, the database, and application were managed proactively.

But when the disaster strikes, the entire setup and years of experience is put to test.

Therefore, it is mandatory to take all the precautionary measures to keep a check on the database. Also, care should be taken to draft a defined disaster recovery plan if the server or SQL application or database goes down. The project should not depend on restorable backup, entirely.