Friday, April 29, 2011

System Table corruption renders to “All system tables must be in....” Error

Whenever a DBA creates any MS SQL object (be it a table, view, report, form, etc.), its properties (metadata) are saved in System Tables. These system tables are primarily used to discover differences and similarities between tables and columns at the time of database upgrade, view performance statistics, and collect lock information. These tables are hidden and even with full DBA access rights, remain restricted. (This is because if a DBA changes the location of any of these tables then the database becomes unmountable). While nothing can change the location of system tables, logical damage in MS SQL Server database can. In such cases, the DBA needs to perform restoration from an updated online or offline backup. But, if in case the database backup is unavailable, then the DBA can execute DBCC CHECKDB command to repair the database. The command, while successful in most cases, can fail under many situations, like severe database damage. For such scenarios, the DBA needs to opt for an effective MDF recovery application.

To elaborate the above case, consider the below error message that appears when a MS SQL Server DBA attempts to mount his/her database:

“System table 'OBJNAME' (object ID O_ID, index ID I_ID) is in filegroup FG_ID1. All system tables must be in filegroup FG_ID2. ”

Read More:

No comments:

Post a Comment