Advertisements

Archive

Posts Tagged ‘Database Corruption’

Troubleshooting Steps For Corrupt or Suspect Databases

Problem

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.

Tip:

  • 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.

Conclusion

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.

Advertisements

Know How to Resolve Microsoft SQL Server Error 5172

May 26, 2016 1 comment

Overview

SQL Server is a relational database management system, developed by Microsoft that is used for storage and retrieval of data whenever required. Database on SQL Server contains one master data file (MDF) and may be associated with number of new data file (NDF). Each MDF file is attached with a separate transaction log database (LDF). Sometimes, when user tries to attach the MDF/LDF database file, an Error may be encountered displaying error message “The header for file ‘xxx.mdf’ is not a valid database file header. The FILE SIZE property is incorrect. (SQL Server Error 5172)”The page will be discussing about the same error and solution on how to resolve MS SQL Error 5172.

What is Microsoft SQL Server Error 5172?

One of the most common error seen by the user of SQL Server while they are trying to attach the MDF or LDF Database from existing system to a new system. User needs to copy the database from the source system onto an external drive, attached the external drive to target system and copy them onto new system. Some of the possible causes of this error are damaged or corrupt log file/data file, attaching higher version database to a lower version SQL Server etc.

Precautions to Avoid MS SQL Error 5172

  • The Database files of the source system should only be copied when SQL Server is properly shutdown or Database is detached.
  • The Release and build number of SQL Server must be either exactly same for both source & target or the target must be higher. A database detached from SQL 2005 version needs to be attached to the same or higher version only.
  • Presence of UNICODE type database attached to the target server, that user no longer wants to use. It should either be detached or renamed or delete it permanently.
  • Attach the database file copied from source to target system using the sp_attach_db command or use SQL Server Management Studio while SQL Server is running. No files of an existing database should be replaced while SQL Server is down.

How to Fix SQL Server Header Error 5172

SQL Server Database files store their data as pages, each page is 8KB. The first page is called file header page containing the most important information about the whole file, like file signature, file size, compatibility, etc. Error occurs when certain value in header information in the boot page (page0) of LDF/MDF file is incorrect. It is also possible that file header page is damaged/corrupted making it unrecognizable by Microsoft SQL Server.

The user cannot attach the database without fixing the file header page manually. Let us consider a sample header of page 0:

Error 5172 Header

Some of the modifications that need to be made in the above header file using Hex Editor are:

  • Value at offset 0X00 (header version) should be 0X01
  • Value at offset 0X01 (m_type) should be between 0X01 and 0X66
  • Value at offset 0x04 (m_flagBits) should not be 0x02
  • Value at offset 0x18 (m_objId) should be 0x63 or higher
  • Combined value at offset 0x1E and 0x1F (m_freeData) should be 0x60 or higher (m_freeData is the offset where the next record will be placed)
  • Page size (0x2000)– m_SlotCnt * 4 should be higher than m_freeData. If not, any subsequent operation will overwrite already existing data & is disaster to SQL Server and users.
  • Offset 1C-1D (m_freeCnt) should be less than 0x1FA0 (8096). m_freeCnt denotes available free bytes within page, if it is greater then, SQL Server has reason to drop further execution because this will end up outside the 0x2000 boundary.
  • value 0x1FA0 (8096) is derived from page size (8192) – total header byte size (0x60) = 96 = 8192 – 96 = 8096
  • Offset 1E-1F (m_freeData) should not be zero
  • Offset 16-17 (m_SlotCnt) should less than 0xFD0 (4048)
  • The value at offset 0x40 thru 0x5F should be zero

After the changes made in the entries of header file using Hex Editor, user can try to re-attach the SQL Server Database files to overcome the issue of SQL Server Error 5172.

Conclusion

The blog will be discussing about one of the most common error encountered while user tries to attach their SQL Server Database files. The in-depth details of the SQL Server header error 5172 with the possible causes have been described. Some of the precautions that should be kept in mind are also discussed. The Error occurs when the header of the database file is invalid, which is why we need to make some modifications in the header file entries using Hex Editor.

The above elaborated description is sufficient enough, but if it fails to fix The header for file ‘xxx.mdf’ is not a valid database file header. The FILE SIZE property is incorrect then you can go with SQL Database Recovery Tool which allows you to recover data from corrupt database file.

sys.foreign_keys does not have matching row in sys.indexes

November 19, 2015 Leave a comment

Running DBCC CHECKDB you are getting following error message:

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes

This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK constraint (FK object_id 2040565179).  This should not happen, SQL Server will not allow you to drop a constraint that is referenced by FK.  If attempted should get following error message:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘dbo.a.NonClusteredIndex-20151119-085219’. It is being used for FOREIGN KEY constraint enforcement.

So if we are suppose to get errors? Why do we have corruption; simple answer, someone be making updates to system tables directly, which is not allowed or supported!

Actually we are not able to update system tables in SQL Server 2005+ (ref), however in SQL 2000 days, we had setting called allow updates in sp_configure options.  Also supported by the KB2787112.

So question is how do you fix it?

First, identify the child table name from sys.foreign_keys:

SELECT object_name(parent_object_id) AS TableName
FROM sys.foreign_keys
WHERE name = 'FK_b_a'

Second, script our constraint definition:

  1. Find the table, we got in SQL Statement above.
  2. Go to Keys.
  3. Right click on FK constraint name.
  4. Script Key As.
  5. Create To.
  6. New Query Window.

Third, drop the FK constraint:

ALTER TABLE [schema].[tablename] DROP CONSTRAINT [fk_constraint_name]

Fourth, Re-create the constraint, with script generated in Step 2.

If it was issue of someone playing around in system tables, this should resolve it.  However, if you get error similar to below:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.a’ that match the referencing column list in the foreign key ‘FK_b_a’.

This means, that the key is missing in parent table and appropriate index needs be created before FK constraint can be created.  Since SQL doesn’t allow the index to be dropped there most likely are other corruption issues that have gone unnoticed.  If that is an issue, you will have to rely on your backups for recovery.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

%d bloggers like this: