DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Database Corruption: Causes, Prevention, and Fixing Techniques

SQL Database Corruption: Causes, Prevention, and Fixing Techniques

If you're facing a corrupt MS SQL database, then you'll want to have a look at this post for some tips and help.

Andrew Jackson user avatar by
Andrew Jackson
·
Aug. 03, 18 · Opinion
Like (2)
Save
Tweet
Share
7.76K Views

Join the DZone community and get the full member experience.

Join For Free

“I had a Database File and I want to attach this MDF file to SQL Server 2014. However, whenever I try to attach the file, I am receiving following error message :
The header for file ….\MSSQL\DATA\xxxx_data.mdf’ is not a valid database file header. The FILESIZE property is incorrect. (SQL Server Error 5172). After surfing a lot, I found that MDF file has corruption in its header. I tried expert solution given in various forum websites but failed to fix header corruption, I am still getting the same error message. I have no idea how to deal with SQL database corruption. Can anybody suggest me any solution to fix this error? Thank you in advance!”

SQL Server stores its physical database in MDF file and the first page of this file comprises the information of MDF file header. The header page keeps the information about the entire database like file signature, size etc. While attaching the MDF file in SQL Server, sometimes users encounter 5172 error code. This error generally occurs when the MDF file becomes corrupted or damaged. This error mismatches the information of the header and takes database into the inaccessible state. Thus, in this problem tackling blog, we are going to describe some easy and cost-efficient ways of dealing with a SQL database corruption.

Before moving on to the solution to fix the database corruption, let us discuss what steps should not be taken if you are facing a SQL database corruption.

What Not to Do?

Do Not Reboot Server

Rebooting the server can only help to fix the minor issues from the OS end. If the issues are from the server's end, then rebooting is not an appropriate solution. Rebooting the system will put the database in offline mode and will detect it as a SUSPECT. This will make the situation worse.

Do Not Shutdown SQL Server

After identifying the master database corruption in SQL Server, every user tries to shut down the SQL Server, yet this is not a proper solution, as the database becomes inaccessible if this is done. 

Do Not Detach/Re-Attach Database

If corruption is present in the database, then detaching and re-attaching the database will make the recovery process much harder.

Do Not Upgrade SQL Server

Upgrading the SQL Server to fix the SQL database corruption is not an accurate method because upgrading the SQL Server version can create a new and major hurdle.

Do Not Run Repair Command Instantly

The DBCC CHECKDB command should only be run when all other methods fail. In many cases, this command takes users to permanent data loss condition. Thus, before executing this command, make sure you have correct knowledge of its syntax.

Solution to Fix SQL Database Corruption

Keeping regular backups and utilizing them later to recover the database in case of corruption or other disasters is the best option to deal with a SQL database corruption. Nevertheless, it is not always a possible solution to restore all the data, as there is always a missing portion of information between the last backup and time of disaster.

The Final Note

There could be various factors that are responsible for the corrupt database like sudden shutdown, hardware failure, virus attack, etc. In this write-up, we have covered various causes responsible for the corruption and what steps should not be taken when your database is in SUSPECT mode. The article covers a quick solution to repair corrupt SQL database file.

Database sql

Published at DZone with permission of Andrew Jackson. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • The Quest for REST
  • How To Create and Edit Excel XLSX Documents in Java
  • ChatGPT: The Unexpected API Test Automation Help
  • Fraud Detection With Apache Kafka, KSQL, and Apache Flink

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: