Allocated to GAM / SGAM ??

In one case of fixing the damaged database, I found this puzzling error :

Server: Msg 8904, Level 16, State 1, Line 1
Extent (1:8384) in database ID 17 is allocated by more than one allocation object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (1:8384) is allocated to ‘GAM’ and at least one other object.
Server: Msg 8913, Level 16, State 1, Line 1
Extent (1:8384) is allocated to ‘TEvents’ and at least one other object.

As much as I can comprehend, this error means that this page is actually allocated to at least 2 objects. One object is ‘TEvents’ which is certainly mine, and another object is totally alien to me.

It says ‘GAM’ object. If I checks to the sysobjects, there is no object named GAM. After doing some research on the net, this error message is always ended with GAM or SGAM. In my current knowledge, I’ve not yet found other that those string value.

Actually I can ignore this, since either object named GAM or SGAM is not of my concern. But certain after performing certain steps to verify this error I came to have some insight of the gist of this error.

First, I am performing DBCC PAGE on that page :

DBCC TRACEON(3604)
DBCC PAGE (‘edoc’, 1, 8384, 1)

Allocation Status
—————–
GAM (1:2) = NOT ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:8088) = 0x0   0_PCT_FULL                     DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED

This means that the allocation bits at GAM (Global Allocation Map) for page 8384, which is marked as not allocated (bit value 1), if the bit value in GAM page is 0 but the bit value of SGAM (Shared Global Allocation Map) is 1 the error should be allocated to ‘SGAM’ 🙂

Then using the DBCC EXTENTINFO for the related table :

gam2

The physical checks to page 8384 yields that first page contains some data, so, the bits in GAM page, the pg_alloc and bytes in PFS page should be change accordingly, to get rid of this error.

By manually changed the GAM bit for page 8384 to marked it as allocated will get rid of this kind of error. How ? Please refer to another post regarding this 🙂

So, what is the meaning of allocated to ‘GAM’/’SGAM’ ? Basically it means, that if we continue to operate this defected database, data pages in 8384 will be deemed to be erased unless some preventive action should be done to correct this problem.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: