Fix SQL Data Error Cyclic Redundancy Check – CRC Error 23 Resolved

We all know that SQL Server is a database that can store & manage enormous data files. Therefore, I/O subsystem issues can cause SQL data error Cyclic Redundancy Check in the server. SQL is quite a sensitive DB when it comes to its disk subsystems. As it plays a major role in the performance, speed & storage of the database, any error in it will most likely hinder the usual working of the database.

SQL backup error cyclic redundancy check is getting more common among SQL users & this is why we’re coming up with this article. Here, users will get to know about the error, its causes, as well as the various fixes.

Triggering this error is not that tough. It’s mostly observed while executing one of the four major tasks in SQL. 

• Starting SQL Server Database
• Database Querying
• Restore Tasks
• Backup Tasks

Now, let’s understand the overview of CRC in detail to proceed further with the utmost understanding of all the technicalities.

What is SQL Data Error Cyclic Redundancy Check Error 23?

Cyclic Redundancy Check is code for identifying errors for data verification processes. It indicates the issues or inconsistency within the storage files or hard drive components.

Both hard drive or the actual file can be the main culprit behind this error. Therefore, users are advised to move the file to another drive to check the reasons. Here, if users experience issues in copying the file, it indicates that the file is having problems. Otherwise, the problem is there in the drive.

Systems use algorithms to execute tasks. Well, when a computer needs to verify the data on storage devices such as SSD, HDD, CDs, Magnetic tapes, etc, it uses the CRC algorithm. That’s all that users need to know about the CRC for now.

Also Read: Fix Index Corruption in SQL Server Database Easily

Why Does Read Failed – 23 – Data Error (Cyclic Redundancy Check) Occur?

Whenever users face any issues of data storage or transmission in the SQL Server database, it is more likely to end up giving this cyclic redundancy check error in the system.

Therefore, the right way to deal with it is to understand all the causes first. Evidently, below are the most common reasons for this error mentioned:

• Issues in the Storage Media: The CRC error is directly linked with the issues in the storage media where users keep their data files. Social Area Network (SAN) or Bad sectors on a hard drive are responsible here.
• Severely Damaged Data Files: We’re well aware of the fact that MDF or NDF files are crucial for databases. Therefore, having corruption on these files might invite CRC error for sure.
• Corruption in Backup(.bak) Files: Just like the major files, if the backup or log files are facing corruption issues, users can face the operating system returned error 23(data error (cyclic redundancy check).
• The Network Transmission Errors: While users perform data transfer over certain networks & observe CRC issues, there are errors in transmitting the actual data. Network integrity plays a crucial here.
• Power Outages/Abrupt Shutdowns: Now, we have a common issue that is sudden power outages or abrupt system shutdowns. Well, this can cause almost any type of error in SQL as it does not let the system recover from the loss.
• Virus or Malware Attacks on System: Virus or Malware are a common story nowadays. Various kinds of SQL injection attacks are getting more frequent & severe. Therefore, users must be ready with preventive measures.
• Poor Hardware Infrastructure Health: Issues with the Server’s Motherboard, old dusty cables, RAM, & unmaintained server also results in issues leading to the CRC error for users for sure.

SQL Server CRC Error Actual Scenarios

There could be N number of situations where users face this SQL error. However, we are going to mention two of them here.

Case 1: Database Querying Actual Situation

Database querying is the most basic task that we all execute in SQL. The one having the database will obviously execute queries to extract & add data. Therefore let’s undergo the scenario of database querying.

Whenever a user executes any query & the database stops in between along with the data check error. Then users can witness the SQL error 23 data error cyclic redundancy check in SSMS (SQL Server Management Studio). Users face the below-mentioned error:

Server: Msg 823, Level 24, State 2, Line 1

I/O error 23(Data error (cyclic redundancy check).) detected during read at offset 0x000001ac1c4000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2K16\MSSQL\DATA\Major.mdf’.

Case 2: Backup Scenario to Fix SQL Cyclic Redundancy Check Error

Now, let’s have a look at the backup scenario. Users also get this error while taking a backup of the SQL Server database. Going through the SQL Server logs can help users have a clear view of the error details. Let’s understand SQL data error Cyclic Redundancy Check with an example.

10/18/2016 12:00:19 AM Creating backup of Major to C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2K16\MSSQL\Backup\

10/18/2016 12:00:32 AM ERROR: Read on “C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2K16\MSSQL\DATA\Major.MDF” failed: 23(Data error (cyclic redundancy check).)

BACKUP DATABASE is terminating abnormally.

10/18/2016 12:00:32 AM ERROR: Job finished (With Errors)

#1 Fixing SQL Cyclic Redundancy Check Error I/O Issues with CHKDSK

Before we come to any conclusion, all we know is that it’s an I/O disk error. Therefore, first, we’re going to look at the I/O error solving. Fixing the storage issues of I/O subsystems will most likely solve the error in the first place. If not, then we have other solutions as well.

Step 1. Simply Run the CHKDSK CMD on the suspected disk. Then using the /F parameter, allow it to fix any existing errors. It is shown in the below image accurately.

Step 2. Execute full defragmentation of the disk once the errors are fixed with the help of CHKDSK CMD to fix SQL server CRC error.

Step 3. Execute Data Integrity Check on the Major.mdf file. This will help users verify whether the data is in a healthy state or not. The below command will help users do that.

DBCC CHECKDB (Major) WITH NO_INFOMSGS, ALL_ERRORMSGS

After running the command, in our case, we found that there were still some errors left.  We got the below message & users might also face this one. Let’s understand this to solve SQL data error Cyclic Redundancy Check with ease.

Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:72864) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
Server: Msg 8921, Level 16, State 1, Line 1
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:72864) with latch type UP. failed.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:72864) with latch type UP. failed.
Server: Msg 8998, Level 16, State 1, Line 1
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 8 pages from (1:72864) to (1:80879). See other errors for cause.
CHECKDB found 2 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 2 allocation errors and 1 consistency errors in database ‘Major’

Step 4. Here, we came to know that the database has corruption in some of the files. Therefore, we have two options here. Either we can just restore the latest backup of the database file. Or, the second option is to just fix the corrupted data.

#2 Repair & Restore Corrupted Elements of SQL

To fix the SQL data error Cyclic Redundancy Check completely, we need to get rid of the corruption issues. Therefore, let’s try the restoring database option first.

Use the RESTORE VERIFYONLY command to find out the consistent state of the SQL database. The CMD is mentioned below.

RESTORE VERIFYONLY FROM DISK = C:\BackupFile\MoreData.BAK
GO

Read: An Inconsistency Was Encountered In The Requested Backup File Error Fix

In case this does not work & users want to fix the files, below is the subsequent command mentioned:

USE master;
ALTER DATABASE [Database_Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC CHECKDB ('Database_Name', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE [Database_Name] SET MULTI_USER;
GO

#3 Fix Data Error Cyclic Redundancy Check with SFC Scan

Now, as we know that the problem is either in the drive or file. For the drive, we have the CHKDSK command. However, if the issues are there in the files, we need to verify that as well. Furthermore, we can the SFC or System File Checker method to fix such issues. Just follow the below steps to fix any issue here with ease.

1. Open Command Prompt using the Admin Account (For All Privileges).
2. Run sfc/scannow command in the cmd window & Hit the Enter button.
   
   
3. Wait for a while till process completes. Run Exit CMD & Click on Enter.
4. Users just need to restart their system to see if their problem is solved.

That’s it. If the problem is still there, then we can proceed to the last manual solution which is DRIVE formatting. Let’s move to that.

#4 Drive Format Method to Fix the Hard Drive from Root

First of all, this method is not for everyone. If there is not any important piece of data present in the drive, then only users should do this. Otherwise, if there are crucial files in the drive, we do not recommend this solution. It will completely vanish all the data files for sure.

There are cases when a user’s device becomes completely inaccessible. That is when they should opt for this solution. Here. we’re going to reset the file system back to a readable format. It has solved CRC errors in plenty of cases.

Step 1. Open the Windows Explorer (Win+E Shortcut Key) to begin.

Step 2. Right Click the Drive with issues & Click the Format option.

Step 3. Select the desired File System as FAT32 or NTFS here.
(Note: FAT32 is recommended for SD Card or USB smaller than 32 GB in size. However, NTFS for external disks or hard drives.)

Step 4. Click on Quick Format “CHECK BOX” & Hit the Start button.

After that, once again verify if the error 23 data error (cyclic redundancy check) has been solved or not. If the problem is still there, then there must have been corruption in the database files. Therefore, using the automated tool method mentioned above is the final & sure shot solution.

#5 Fix SQL Server CRC Error If Manual Method Fails

This tool can fix damaged data files as well as recover even the deleted data of SQL. With the endless advanced features, users can get the best solution without any hassles.

Step-1. Download the Software & then begin after installing.

Step-2. Hit the Open button to Add MDF or MDF files here.

Step-3. Choose the Scan Mode as Quick or Advance further.

Step-4. Set the Destination Platform & Authentication type.

Step-5. Finally, Hit the Export/Save button to complete.

Conclusion

Fixing the SQL data error Cyclic Redundancy Check is not that tough for users if the right tool & technique are known. Otherwise, users without technical proficiency will face several issues to get the desired result.

To fix the corruption issues, an automated solution is the right choice. Manual ways to solve the SQL Cyclic Redundancy Check error corruption issues do work but are not reliable at all. Users need to make their decisions wisely to avoid any further hassles.