June 06, 2011

Allocation units in SQL server

In Microsoft SQL Server, the database is basically divided into different allocation units, viz. Index Allocation Map or IAM pages, Global Allocation Map or GAM pages, Shared Global Allocation Map or SGAM, and or Page Free Space or PFS pages.
SQL Server uses the IAM pages to find the extents allocated to the object.
For each extent, SQL Server searches the PFS pages to see if there is a page
with enough free space to hold the row.If SQL has to allocate new space, the GAM and/or SGAM is used. This would
occur AFTER SQL has determined that there is not enough space to insert the
new row on pages already allocated to the object.
All of these allocation units are required to be in healthy condition for the successful functioning of your MS SQL database. However, corruption in any of these allocation units or pages can result in stopping SQL database mounting; thereby, resulting in the inaccessibility of databases elements like tables, views, stored procedures, indexes, triggers, etc.
Various factors can be responsible for such corruption in SQL Server allocation pages. Naming a few of them – wrong system or application shutdown, MS SQL Server damage, Trojan infections or virus attacks on the system, software malfunctioning, human errors, media errors, etc. However, in the above situation, the major possibility of damage is due to the corruption in any of GAM, SGAM, or PFS pages of SQL database Server Meta structure.

For resolving the issue and for regaining access to your SQL database elements again, you can do the following measures:
* If corruption has occurred due to physical damage, then you must replace that system component which is damaged by a new component
* However, if the problem is as a result of some logical damage, then you can run the ‘DBCC CHECKDB' command with correct repair clause
* In case above resolutions fail, try to restore your damaged or deleted SQL database from an updated backup

No comments: