Skip to content
English
More support Sign in
Contact us
  • There are no suggestions because the search field is empty.

How To Fix Database Corruption. (LEVEL 2 ONLY)

Search

How To Fix Database Corruption. (LEVEL 2 ONLY)

If you are getting an error that looks like what is printed below during FTPAutobackup or any other time, it is very possible that the database is corrupted. 


Failed to backup database BackupDatabase1 reason: SQL Server detected a logicalconsistency-based I/O error: incorrect pageid (expected 1:2142; actual 0:0). Itoccurred during a read of page (1:2142) in database ID 4 at offset 0x000000010bc000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. Could not insert a backup or restore history/detail record in the msdb database.

 

Initial Steps:

  1. Back up the DB. ( For this example, MSDB is the database the was corrupted)
  2. Open a new query under msdb database. 
  3. Run: DBCC CHECKDB ('YourDatabase') WITH PHYSICAL_ONLY
Back up the DB.
    • This script checks for errors in the system. It does not adjust anything. Also the Physical only allows for the script to run faster and skip the fluff logs. 
    • It will give you the results in the Messages and also may tell you the minimum repair option. 

Repair:

There are 2 levels of repair that can be done. The above script should tell you which to run, but if it does not, run them in order. You will also have to set the database into single user mode to run the scripts. 

  • To set in Single_User: Right click on DB > Properties > Options > scroll to the bottom> Change Multi_user to Single_User. Click OK and yes to close all sessions. Close all prior Queries. 
  • Open a new Query Window. NOTE: In single user, you can only have one window running for single user, So the Properties is 1 window and the Query is 1 window, so 1 has to be closed for the other to open. 
  • 2 Query Option:
    • DBCC CHECKDB('DB Name', REPAIR_REBUILD)
      • This query tries to repair the corruption without removing any data. This is the preferred script since nothing is removed. However, it is normal for the more extreme option to be required. 
    • DBCC CHECKDB('DB Name', REPAIR_ALLOW_DATA_LOSS)
      • This query is highly dangerous and should only be ran by a Level 2/Developer. It involves removing data and replacing it to fix inconsistencies in order to fix corruption. Above all else a back up must be taken before running this script to ensure we can restore if necessary. 
      • After this script is ran, copy and save the message to a file on the desktop so we can look back if needed.
    • Re-run DBCC CHECKDB ('YourDatabase') WITH PHYSICAL_ONLY
      • You should see different results confirming the corruption was resolved.
  • Change the Database back to Multi_User
  • Log off all terminals and restart Main. 
  • Open a terminal and check basic functionality. 

 

If you run into any issues, address other Level 2/Development. 

Related Articles