An Insight To IAM (Index Allocation Map) Pages in SQL

The blog post sheds light on the IAM pages used in SQL. SQL is the most preferred database manager and storage unit. The Index Allocation Map pages aka IAM pages are special kind of pages that are deployed to keep track of the allocation unit.Before moving on further with IAM pages let us just get a brief of what actually is an allocation unit.

SQL Server organizes data like tables, index, large object and other data in pages. Also the pages that store tables are not just assigned to tables itself. An allocation unit basically stores all these pages. As we know that SQL is not immune to database corruption, similarly the problems in these IAM pages also lead to SQL database corruption. So for the solution SQL Repair need to be considered.

What Is An IAM Page?

As mentioned earlier, IAM pages are used to assemble all the pages stored in a single allocation unit. The number of IAM pages in an allocation unit depends on the size of allocation unit. But there is atleast one IAM page in every allocation unit. The IAM Page has type 10. An IAM page normally accesses 4 GB space in a single file. These chunks of 4 GB space are referred as GAM intervals. 

Detailed Analysis Of IAM Page

Every IAM page has two sections-Page Header and Bit Map.

·         Header

The size of an IAM header is 96 bytes. Index allocation map header containing a base page address follows this page header. This header acts as a pointer to the first page of the index allocation map. Each IAM header consists of eight slots for mixed content page allocations.

·         Bit-Map

The other part of an IAM page is the 8000 byte bit map. The first bit of this bit map covers the area containing the page which is pointed by the first page pointer. It is not necessary for the IAM page to be located in the same interval as GAM intervals. 

Header Fields Of IAM Page

·         sequenceNumber

It’s the position of IAM page in an IAM chain. This increases by 1 whenever a new page is added.

·         status

This remains unused in the header.

·         objectId

It is the object ID of which IAM page is a part.

·         indexId

It is the index ID of which IAM page is a part.

·         page_count

It is the page IDs used by single page allocation array.

·         start_pg

It is the GAM interval mapped by the page.

·         Single Page Allocation array

These are the pages allocated from mixed extents.

Summary 
IAM Pages are used by SQL Server to keep track of allocation unit. Each IAM pages consists of two sections- Header and Bit map.

SQL MASTER DATABASE SHRINKING

With the growing technology the files size needed to store the information about these technology has been increased. Same is the case with SQL database as with technology size of databases is also becoming bulky so there is need to reduce the size of the database. As SQL support mdf, ndf and ldf files, but the main and major drawback is that SQL is prone to corruption so for the solution can use SQL Repair. At the time of corruption larger the database more trouble to recover it so for that shrinking must be done in SQL database. In SQL database shrinking is done to reduce the size of database file that allow utilizing the unused space in between the file. Database file shrinking can be done for both log files as well as master files and can be done in two ways automatic and manual.

Database file shrinking is done from end. So if u wants to shrink a 5GB file and for which the target size given by the user is 4GB in DBCC statement then the shrinking process of file will be done from end the database engine will shrink from end 1 GB of the file.

Automatic Shrinking

In this if the Automatic Shrink is enabled then the database engine routinely shrink the database file which posses free space. For this AUTO_SHRINK option has to be switched or can say set ON. This option will perform its function in the background which did not affect the user.

Manual Shirking

DBCC SHRINKDATABASE as well as DBCC SHRINKFILE statements can be used to shrink database files manually. This process of shrinking database files can be stopped at any time in between. DBCC SHRINKDATABASE statement can only shrink your database to size which is not smaller then it’s initial size, even if u delete all the data from the database. DBCC SHRINKFILE

can be usede to shrink the file and can shrink the file to smaller size than its initial size.

So finally can conclude that database or file shrinking should be done to utilize the unused space within the database or file that will reduce overall size of the database.

SQL SERVER DATABASE FILE AND FILEGROUPS

SQL Server handles the bulky database of organizations. SQL supports two operating files one is data file and another is log files. There are three different formats for each file mdf and ndf file formats are for data files and all logs files are stored as .ldf format. The data file contains all the records, tables, indexes, views and stored procedure and all the transactions taken place in the database are being stored in log files. Later these log files can be used to recover all the transaction done in the database. As SQL Server is the best application but the default fact is that this is not immune to corruption. Mostly the issue involve in corruption is the repair of database so for this user mostly go for manual repair but there are certain issues involved with manual solution which may leads to failure of the manual solution in recovering database. So SQL Database Repair can be used in this case.

Types of database Files in SQL Server

SQL support three file formats for data and log storage. For the primary data .mdf files are used it store the master data and also known as master data files and for the secondary database .ndf files are defined. All the transaction information or can say log data are stored using .ldf format and referred as log files or transaction log files. Basically your secondary database files are optional one and the master database files are the permanent one. That means a database will surely have one .mdf file but the existence of .ndf is optional.

Types of Filegroups in SQL Server

SQL Server supports two filegroups. There is a primary filegroup which exist for every database and secondary filegroups are being created by the user. The primary filegroup contains all the primary files and system files are also allocated in the Primary filegroup. The user-defined filegroup contain secondary files created by the user.

SQL SERVER ERROR 8910

"Page P_ID in database ID DB_ID is allocated to both object ID O_ID1, index ID I_ID1, and object ID O_ID2, index ID I_ID2."

The above shown error message appears in SQL server and referred as Error 8910.This error is linked with Index Allocation Map (IAM) pages. These pages are used to heap or index tables in SQL database files. IAM pages support two types of entries IAM header and a bitmap. This error is basically encountered when there are inaccurate entries in IAM pages. That mean when in IAM array there are two entries of same page or one page has two allocated indexes. In SQL Server corruption is a common issue and Error 8910 is one of the issues causing corruption of database. User mostly go for instant manual solutions as he/she detect the error such running DBCC CHECHDB command, try to restore from backup or he/she will be going for Hardware diagnose to solve the issue and to recover the corrupted database.

Manual Solutions to Solve 

As discussed in the previous section manual solutions to resolve this error are to go for DBCC CHECKDB command, back-up restoration or hardware diagnose. But in mostly cases these solutions are success but what if these solutions are not able to repair the data. So to know more visit my Error 8910 blog.

SQL SERVER ERROR 5228

Error: 5228, Severity: 16, Table error: object ID %d, index ID %d, partition ID %I64d, alloc unit ID %I64d (type %.*ls), page %S_PGID, row %d. DBCC detected incomplete cleanup from an online index build operation. (The anti-matter column value is %d.)

Most of the database user’s are customary to this error message while using the database. The Error 5228 occurs when intermediary state leads to the corruption of database. In SQL Server occurrence of errors while managing bulky data is a customary event, but the data recovery is crucial issue.

Reasons of Occurrence

SQL Server error 5228 occurs when intermediary state leads to the corruption. That means the database become in-accessible and display error message 5228 as shown above. As seen mostly the error 5228 occurrence is seen in SQL Server version 2000 and all above. Whenever the user perform delete, modify and add command on database and a partition index that is unfinished is identified in the PN_ID, O_ID object and I_ID index partition then intermediary state occurs leading to error 5228.

SOLUTIONS 

Mostly the user when identify this error state go for manual solutions such as Hardware diagnose, Backup restoration and run DBCC CHECKDB command. Most of the time these solutions are able to recover data but in some cases where these manual solutions are not a success SQL Repair can be helpful and to know more read  Error 5228 Blog.

SQL SERVER ERROR 8930

“Table error: Object ID O_ID1, index ID I_ID1 cross-object chain linkage. Page P_ID1 points to P_ID2 in object ID O_ID2, index ID I_ID2.”

SQL database user many times encountered an above shown error message while using inbuilt utility DBCC CHECKDB database in ease to recover lost data. This error message displayed is known as error 8930 encountered in SQL database. SQL is referred as best database management and storage application used by the organizations. Occurrence of error 8930 is sign of corruption in database, so data recovery is important issue to tackle.

CAUSES OF ERROR 8930

The main cause of the occurrence of this error is the corruption of the database which doesn't allow the DBCC utility to recover data.

The incorrect page ID reference as shown in error message “P_IDI page refer to P_ID2” is the main cause of the error 8930. Page header corruption, hardware fault and virus attacks can be also are a main reason behind the occurrence of t his error 8930.

SOLUTION

If the error occurs the main concern is to recover data by resolving this error, mostly there are two ways to resolve the error. One is to go to manual solution and another way is to go for repair tool. Manual solutions to resolve this error is to perform hardware diagnose, back up restoration and to know more go to Error 8930 blog. 

SQL MASTER DATA SERVICE

Microsoft SQL Server is the most preferred database management system use by the database user’s and large enterprise organization to manage and store their useful data effectively.Microsoft SQL Server support three file formats for different data. Primary data are stored in the form of .mdf file, secondary data is being stored in the .ndf file and all the transaction log files are stored in .ldf files. The master database management is done by the Master Data Service provided by the Microsoft SQL Server. As seen that the management of the large bulky database is not that easy it, as it not a doubt on the efficiency of Microsoft SQL Server but the unfortunate truth is that this most efficient database management and storage application is not immune to the corruption. So sometimes these master database files get corrupted so in that case user goes for a SQL Repair.

WHAT IS MASTER DATA SERVICE?

All the efforts done by an organization to define and discover the list of data (non-transactional lists) are described by the master data management to compile the maintained master lists. So the Microsoft SQL Server solutions for MDM (Master database Management) is your Master Data Services. Master Data Services can be used for managing Domain (As the service is not specified to managing lists of products, customer or accounts).

Components and Tools of Master Data Services

The Master Data Services offered by Microsoft SQL include many tools and components which are listed below.

1.  Master Data Service Add-in (For Excel):- Used to create new attributes and entities as well as manage the data.

2. Master Data Service Configuration Manager: - A tool used to configure and create MDS web applications and database.

3. Master Data Manager:- Basically a web application used to perform tasks (administrative) used by the user to update data

4. Master Data Services Web Service: - Used by the developer to build or extend custom solutions that are used by Master Data Service.

5. MDSModelDeploy.exe:- A tool used to make packages of the model objects and data which can be used in other environment.

SQL Server Transaction Log

Thinking of a database management system, a mission critical application and a breakthrough perform, the first application which strikes the mind of the user is SQL Server. SQL Server is best rated database management application which provide storage and management of database. SQL support three different file formats, MDF for primary database NDF for secondary database and LDF for the transaction logs or can say for log database. At the time when your database gets corrupted you can recover your full data by using the transaction log saved as .ldf format.

Note: - But sometimes this backup is not maintained so then at the time of corruption other methods are used to repair corrupt data.

Transactions Log: - Basically transaction log contain all the records of the transaction operations done on your database. Truncation should be done on transaction log to reduce the size of these files.

Supported Operations by Transaction:-  All the operations supported by transaction log are listed below.

•   Individual transaction recovery.
• Transaction replication support.
•  It recovers all pending transactions when SQL Server is started.
• Database mirroring, log shipping and Always on groups are some recovery and               high availability solutions.

Truncation in Transaction log: - Truncation done in transaction log will free the space in the log files. In this process the in-active virtual log files are deleted so to avoid transaction log filling. So truncation of transaction log is an important process to avoid the filling of full disk space allocated to transaction log. The delay in this process can cause problem so there is automatic start of this process by certain events that are listed below.

•  When check-point occurs in the simple recovery model than truncation occurs.
• In the case of full and bulked-logged recovery model when log back-up occurs the truncation of transaction log take place.

As mentioned above truncation take place when virtual log are inactive but sometimes the long activeness of the logs leads to deal in this truncation process. But sometimes we need to analysis the transaction files so for that can use Log Analyzer Tool.