SQL Server Production Database Corruption – Causes & Fixes

SYSTEM DOWN: Database Error 8928 and application not working!!!!

Yes! This is exactly how it looks during the database corruption in SQL Server! And I had to deal with this early in the morning. Let me tell you the whole story! 

I woke up at 5 am, not to my sounding alarm clock but to email alerts and a phone call. 

Hello? Sam – the system administrator on the other end of the line in a panicky voice says: “All east division users are down on the service desk application. 

They can log into the application, but cannot pull up any client’s data. I have already refreshed the app pool, rebooted the application servers, and still cannot get it to work”. 

(Just the usual database corruption in SQL Server scenario! But I was not ready to deal with it at 5! Anyway!)

The database alerting system I created already gave me a lead on what was going on and where to start troubleshooting right away. 

Multiple tickets submitted by the system users provided details as well from the front-end. The screenshot below shows an instance of the issue experienced.

It looks like the application tries to fix database errors encountered, but is still unable to recover from the failure. 

Continue to read to find what the cause of this database corruption in SQL Server was, the quick and, to some, the easy fix.

CAUSE                                        

In this database corruption in SQL Server situation, the impacted application was a system with an  SLA max of 2 hours of downtime. 

Time was not something that I had at that moment, hence I had to immediately provide a resolution and then go back through logs to identify the root cause. 

For the purpose of this article, I will provide the root cause first for the reader to have a better understanding of the solution.

Long story short, one of the email alerts indicated the SQL Server experienced a dirty shutdown a few minutes before the application went down. 

I got a confirmation from the system administrator that some servers (including this database server) had experienced a shutdown due to a major failure on the host machine. 

The SQL Server database went into an inconsistent state due to the dirty shutdown, as there were in-flight transactions during that time. 

If you ever run into this scenario, follow the steps below, and you should be back up and running in no time with respect to server resources and the size of the database.

FIX      

Here’s exactly how you can fix it without any further worry! Just follow my lead! And that’s it!                                         

Identify A Corrupt Database

• Log in to the SQL instance, under object explorer, you would find the database(s) that are possibly corrupted, as highlighted in the screenshot below. 

A suspect database is one that is in an inconsistent/corrupt state.

• In order to identify the corruption, run a DBCC CHECKDB on the database using the query below.

DBCC CHECKDB (corrupt_database_name)

• You should have a result as shown below. It will identify all allocation and consistency errors and show which database objects are corrupted. It will also suggest the minimum repair level for the corruption.  

NOTE: A DBCC repair is not guaranteed, and the database may still be corrupted after the repair.

Restore Corrupt Database

As a database administrator, planning for such disasters is an important part of the job, and you need to have a recovery plan set up that is all ready to roll. 

In my case, the production database was set up in the FULL RECOVERY model, and transaction logs were backed up every 1 hour. 

With this plan, I could successfully restore the database using the point-in-time method. I was able to restore and achieve our Recovery Time Objective (RTO) requirement.

NOTE: The trick here is to restore the database to a different name and make sure the database is consistent and has no other issues. 

You can then rename or drop the suspect database and rename the newly created database to its original name.

RESTORE DATABASE Corrupt_Database_Name 
   FROM CorruptDBBackups 
   WITH FILE=3, NORECOVERY; 
RESTORE LOG Corrupt_Database_Name    
   FROM CorruptDBBackups 
   WITH FILE=4, NORECOVERY, STOPAT = ‘Sep 29, 2019 12:00 AM’; –specify your target time on STOPAT clause
RESTORE LOG Corrupt_Database_Name    
FROM CorruptDBBackups 
WITH FILE=5, NORECOVERY, STOPAT = ‘Sep 29, 2019 12:00 AM’; –specify your target time on STOPAT clause
RESTORE DATABASE Corrupt_Database_Name WITH RECOVERY;

  The restore process, when run, will progress and complete as shown below.

 Check Integrity And Status

The final step is to perform one more integrity check on the newly restored database to make sure there are no issues online.

• Run another DBCC CHECKDB on the database and verify the results as shown in the screenshot below.

DBCC CHECKDB (newly_restored_database)

select name, database_id, user_access_desc, state_desc, recovery_model_desc, * from sys.databases

• As shown in the results below, the restored database is ONLINE, set to the FULL recovery model, and allows multi-user access.

Database Corruption In SQL Server: How It Can Affect A Business? 

Database corruption in SQL Server can massively impact a business. How? Well, it can strongly compromise both the integrity along the availability of the critical data. 

Whenever corruption occurs, whether it is due to a hardware failure. Power outage or software bugs can easily lead to: 

• Massive data loss. 
• Inaccuracy in reporting 
• Downtime. 

As for the businesses, this can be a huge disruption to operations. It can lead to delays in decision-making and financial losses. 

Which is why I suggest: 

• Keeping a backup of all the data regularly, 
• Checking the integrity from time to time, and 
• Taking all the preventive maintenance. 

This way, it will be helpful for you to continue with your business. You will be able to further protect it from SQL Server database corruption. 

Conclusion

And there you go! You can easily avoid problems if you have a solid recovery set up, even during the corruption of the production database or other kinds. 

Now, imagine you just got stuck in a state and found out that the DR plan was not established. Worst part? There is no backup to restore! 

Well, in that case, you can use the minimum repair level suggested by DBCC CHECKDB while testing the integrity check. 

One thing that you must keep in mind is that the repair feature of this server does not provide any guaranteed solution. 

For a quick and more adaptable repair, get the corrupt SQL database back into a working state with minimal to no data loss. Look no further than a professional SQL database recovery tool such as Stellar Repair for MS SQL. 

It repairs faster using an advanced repair algorithm and can access data from the database. Click the mentioned link to learn more!

Read Also:

• 10 Simple Steps To Export Data From Microsoft SQL Server To A CSV File
• The Relationships Between Document Databases & Other Databases
• How to Deal with Exchange Server Crash?