Sometimes, while working on SQL Server, users receive various error messages like SQL Server Error 5172 due to encounter of suspect or corruption in databases. This happens because of operational mistakes or faulty hardware, which can be resolved by using the various actions for corrupt or suspect databases.
Solution for Corrupt or Suspect Database
There are some recommended actions for corruption or suspect databases that help users in handling a situation as discussed below. However, before using these actions users must ensure to have backup strategy that helps to recover the data from failures.
- ERRORLOG File
Firstly, check the ERRORLOG file for SQL Server to find the occurrence of an error. Many times, the data or log file is missing due to which, there are chances of suspect or corruption of databases. During the startup of SQL Server, it will help to encounter the problem.
- NO_INFOMSG Option
User can run the DBBCC CHECKDB against the databases to find the reasons of the occurrence of an error message by showing the error number. There might some specific recommendation for error message. This option is helpful as it return only error message such as:
DBCC CHECKDB (adventureworks) WITH NO_INFOMSGS
- RECOVERY_PENDING option
If the log file is missing then, user cannot run DBCC CHECKDB to perform last log backup. Instead of this, users can use SELECT name, state_desc, database_id from databases. It will help them to more to understand the problem. With this, they can see RECOVERY_PENDING if any database is missing or hindering in SQL Server from executing automatic recovery at startup. It will show all the status for database.
- Hardware Diagnostics
To remove the occurrence of an error, users can run check errorlog, eventlog. If still the error occurs then, they can perform the hardware diagnostics. It will help to resolve the corruption of database issue. If there is some problem detected then, ensure the replacement of faulty hardware.
- Tail Log Backup
The tail log backup contains the transaction log backup, which is backed up most recently. It helps to recover right up to the point of disaster. It makes easy for users as they get all those log records back that was corrupted.
- Repair Command
If the database is not suspected then, run DBCC CHECKDB with REPAIR command. It is a secondary option because it will result in data loss. This command will repair and solve the issue or it will result in loss of whole data.
- Enable Checksum
In a way to detect the corruption issue, users can enable the CHECKSUM verification. It helps them to give the timely alerts when any error is occurred because SQL Server is a robust to check for other disk errors.
- Never detach the suspect database otherwise, all chances are lost to have backup.
- Depending upon the error, manually rebuild non-cluster indexes, drop, and reload table if data is static.
The recommended actions for corrupt or suspect databases help to bring back the data in accessible mode. However, users cannot repair a database of SQL Server in case of unavailable or not updated backup.