MS SQL Server database corruption – how to identify and fix?
Microsoft's MS SQL Server, a relational database management system, is in widespread use across numerous businesses for the storage of their professional information. SQL offers various editions tailored to meet the needs of clients, spanning from small-scale enterprises to large corporate giants.
SQL corruption – common causes
Microsoft SQL Server is susceptible to various risks, including database corruption and errors. In particular, hardware malfunctions frequently play a significant role in causing database corruption. Issues with components such as hard disks, controllers, CPUs, and memory units can jeopardize the integrity of the database, given the SQL server's substantial reliance on these components for storing and retrieving information. The primary contributors to database corruption in Microsoft SQL Server are as follows:
- Various issues can impact the subsystems responsible for storing database information on the memory disk. These problems can result in the incorrect organization of table data within the database, ultimately preventing users from obtaining the information they seek to retrieve. Additionally, software bugs can contribute to SQL corruption.
- While anti-malware and anti-spyware tools are beneficial for safeguarding various files on a system, they can pose risks to SQL databases. Consequently, it is recommended to exempt databases from antivirus scans.
- Several users prefer compressing their databases in order to conserve storage space. However, it's essential to note that compressing database files isn't recommended, as it can elevate the risk of data corruption. Similarly, storing backup files within a compressed database can also amplify the likelihood of database corruption. If a user genuinely desires to reduce the size of the database file, it is advisable to utilize the built-in data compression and backup tools provided by SQL Server.
SQL Server corruption poses a significant threat due to its stealthy nature, as it conceals any indication or error notification of corruption until the user attempts to retrieve data. Consequently, users may remain oblivious to any underlying database issues. To detect corruption in the database, a straightforward one-liner query is commonly employed: "SELECT * FROM msdb.dbo.suspect_pages."
If there are no errors detected in the database, the result will display no rows, indicating the absence of any corrupted entries. Nevertheless, this outcome does not guarantee the absence of potential corrupt data, as the query solely examines entries with previous corruption issues. Any corruption in more recent entries will remain unchecked. A comprehensive and meticulous inspection is essential to identify any undetected issues.
DBCC CHECKDB
DBCC CHECKDB is a commonly employed tool among database administrators for the identification and repair of corrupted files. It encompasses various subcommands such as DBCC CHECKCATALOG, DBCC CHECKALLOC, and DBCC CHECKTABLE. To execute this command, simply adhere to the following syntax: DBCC CHECKDB (database_name).
This command will assess tables, storage, quality, and consistency measures, presenting the results in a list. Any deficiencies, inconsistencies, or corruption will be clearly displayed if they exist.
Check for Database Alerts
The Database Administrator has the capability to configure various alert types, which can notify them of any suspicious activities on the user's end or the occurrence of actions that may pose a threat to the databases.
If you're running SQL Server 2012 or a more recent version, you can take advantage of a feature known as Memory Error Recovery. This feature not only notifies you of errors but also initiates a cleanup process in case database pages become corrupt. It performs optimally with modern processors that support this functionality. In the SQL Server Log, you will find error messages like:
‘Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.’
With this feature, you can overcome the minor corruption issues in SQL Databases.
Page Verify with CHECKSUM for Recovery
The Page Verify option for recovery is configured for each database to specify its read or write type. To activate this feature, navigate to the database and select "Properties" from the right-click menu.
Go to Recovery option, Choose Page Verify, and write CHECKSUM. Then Choose the Target Recovery Time in Seconds and click OK.
Contemporary SQL Server editions automatically enable CHECKSUM verification by default. However, if you've upgraded from an older version to a newer one, you can manually configure CHECKSUM settings. Consequently, each time a read or write operation occurs in the database, SQL Server will automatically apply CHECKSUM validation. In cases of database corruption, attempting a read operation may result in error codes 823, 824, or 825.
Transact SQL cmdlets may not be effective with severely corrupted databases. There is a chance of data loss too. In serious cases of SQL corruption, only efficient third-party Repair MDF tool will be of help. Kernel for SQL Database recovery has been developed to help the businesses to regain their critical business data from SQL databases. It retrieves all the objects of a database (including tables, triggers, and procedures) without losing the relationship schema. It is user-friendly and easy to operate, let’s see how you can recover SQL database with it:
- To start the process, click on the Browse button to select the Database, select and add it from the system.
- After adding the database, click on the Recover button to proceed.
- In this screenshot, the recovery process is in progress, wait for it to finish.
- After a successful recovery, you will have all the items listed on the left-hand side of the screen in the folder tree. Here you can also have the preview of the files on the left side by clicking them individually.
- Now, to proceed with saving, click on the Save button, and select the Saving Modes for the recovered database. There are two modes for this, in SQL server mode, you can directly save the recovered items in the SQL server directly. Enter the server name and database name in the respective fields or simply add the username and password and click on the OK button to proceed.
- The other saving option is to choose the Bath file mode; in this, you need to provide a destination to the recovered objects in the system. You need to click on the Browse button to do so and proceed with selecting a preferred location. Click on the OK button to proceed.
- Clicking on OK will start the saving process, wait for it to happen. You can click on the Stop button to discard the process at any time.
- Once the saving process is finished, you will get the notification. Click on OK to proceed.
Conclusion:
In this blog, we have discussed how you can identify database corruption and how you can recover a corrupt SQL database. In this blog, we have mentioned the manual and the automated method for the database recovery. Both of the methods provide the same results, but the automated method assures the successful recovery.
