SQL Error 9100: Reason & Resolutions

SQL server fatal error 9100 appears when possible index corruption is been detected in the SQL server database. The error message occurs with the severity level 23 and is associated with SQL Server 2005, 2008 (R2) and 2012.

The error message appears something related to this:

"Msg 9100, Level 23, State 1, Line 1
Possible index corruption detected. Run DBCC CHECKDB."

There is no such straightforward resolution procedure available to get rid of the possible schema corruption error. Therefore, some basic workaround methods can be implemented to rectify this error message.

Resolving SQL Server Error 9100

Resolution #1: DBCC Execution:

The associated error message recommends running DBCC CHECKDB for rectifying the issue. Database Console Commands are executed in order to check the physical and logical inconsistencies among database objects. This in fact, testifies following parameters in the database tables:

  • Consistency among pointers.
  • Rational amount of data on each table.
  • Data pages and indexes are linked appropriately.
  • The indexes are properly sorted.
  • The volume of page offsets is appropriate.

This command includes three sub commands and they are summed up herein:

  • DBCC CHECKALLOC: Performs consistency check over the space allocation structure of database on disk.
  • DBCC CHECKCATALOG: It performs consistency checks in the system table of specified database. It ensures that:
    • In syscolumns and systypes, each data type is assigned with a matching entry.
    • In sysobjects, each view and table has minimum of one column in syscolumns.
  • DBCC CHECKTABLE: The command is used to check integrity among text, index, data, images and next pages for the DB table. It performs consistency check over indexed views and tables.

Note: All the above mentioned commands are executed automatically when DBCC CHECKDB is implemented. Therefore, there is no need to run them separately.

Required Permissions

There are certain permissions and authorities required for implementing DBCC CHECKDB on specified SQL database table. The user must be a member of:

  • "sysadmin fixed server role"; or
  • "db_owner fixed database role".

Resolution #2: Restore from Backup:

If the SQL error 9100 is not resolved by DBCC execution; another resolution method must be implemented. Restore database from the most recent backup files as this way, only the latest data will be lost. The full clean backup can help restoring database components back into their original positions.

It is always recommended to backup SQL server databases on regular basis. Also, it is a good practice to store backup data at some off-site location with minimum access. This strategy is application oriented and works within existing and available resources. Backup and restoration strategy involves three important aspects:

  • Planning;
  • Implementation; and
  • Testing

Note: It is important to maintain backup restore operations manual for every database to avoid critical aftermaths.

If the above mentioned resolution methods fails to fix the problem, then you can take the help of profissional third party utility which allows you to recover SQL database and fix SQL server warning fatal error 9100.

Download Now   Purchase Now

sql recovery banner