August 23, 2010 at 6:09 am
Hi
This is the output of DBCC CheckDB statement of our one database
Extent (6:5008984) in database ID 51 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'KM_Test'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (KM_Test).
As per given output , I also have run DBCC checkdb with repair allow data loss but no hope
I also ran
DBCC TRACEON(3604)
DBCC PAGE('KM_Test',6,5008984,3) but did not get the table/data on which this is the problem
output of DBCC PAGE('KM_Test',6,5008984,3)
BUFFER:BUF @0x00000001EDFEB580bpage0x00000001EDAD6000
BUFFER:BUF @0x00000001EDFEB580bhash0x0000000000000000
BUFFER:BUF @0x00000001EDFEB580bpageno(6:5008984)
BUFFER:BUF @0x00000001EDFEB580bdbid51
BUFFER:BUF @0x00000001EDFEB580breferences0
BUFFER:BUF @0x00000001EDFEB580bUse16234
BUFFER:BUF @0x00000001EDFEB580bstat0xdc00009
BUFFER:BUF @0x00000001EDFEB580blog0x21212159
BUFFER:BUF @0x00000001EDFEB580bnext0x0000000000000000
PAGE HEADER:Page @0x00000001EDAD6000m_pageId(6:5008984)
PAGE HEADER:Page @0x00000001EDAD6000m_headerVersion1
PAGE HEADER:Page @0x00000001EDAD6000m_type1
PAGE HEADER:Page @0x00000001EDAD6000m_typeFlagBits0x0
PAGE HEADER:Page @0x00000001EDAD6000m_level0
PAGE HEADER:Page @0x00000001EDAD6000m_flagBits0x200
PAGE HEADER:Page @0x00000001EDAD6000m_objId (AllocUnitId.idObj)17836
PAGE HEADER:Page @0x00000001EDAD6000m_indexId (AllocUnitId.idInd)256
PAGE HEADER:Page @0x00000001EDAD6000Metadata: AllocUnitId72057595206828032
PAGE HEADER:Page @0x00000001EDAD6000Metadata: PartitionId0
PAGE HEADER:Page @0x00000001EDAD6000Metadata: IndexId-1
PAGE HEADER:Page @0x00000001EDAD6000Metadata: ObjectId0
PAGE HEADER:Page @0x00000001EDAD6000m_prevPage(6:5008983)
PAGE HEADER:Page @0x00000001EDAD6000m_nextPage(6:5008985)
PAGE HEADER:Page @0x00000001EDAD6000pminlen56
PAGE HEADER:Page @0x00000001EDAD6000m_slotCnt36
PAGE HEADER:Page @0x00000001EDAD6000m_freeCnt1408
PAGE HEADER:Page @0x00000001EDAD6000m_freeData6712
PAGE HEADER:Page @0x00000001EDAD6000m_reservedCnt0
PAGE HEADER:Page @0x00000001EDAD6000m_lsn(122130:13313:4)
PAGE HEADER:Page @0x00000001EDAD6000m_xactReserved0
PAGE HEADER:Page @0x00000001EDAD6000m_xdesId(0:0)
PAGE HEADER:Page @0x00000001EDAD6000m_ghostRecCnt0
PAGE HEADER:Page @0x00000001EDAD6000m_tornBits1392242637
PAGE HEADER:Allocation StatusGAM (6:4601088)NOT ALLOCATED
PAGE HEADER:Allocation StatusSGAM (6:4601089)NOT ALLOCATED
PAGE HEADER:Allocation StatusPFS (6:5006472)0x0 0_PCT_FULL
PAGE HEADER:Allocation StatusDIFF (6:4601094)NOT CHANGED
PAGE HEADER:Allocation StatusML (6:4601095)NOT MIN_LOGGED
Could anybody please help regarding to solve this issue.
regards
ghanshyam
August 23, 2010 at 6:15 am
What's the output of CheckDB when run with the repair_allow_data_loss option?
Do you have a clean backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 6:58 am
Hi
Same output
Extent (6:5008984) in database ID 51 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'KM_Test'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (KM_Test).
Actually We do Full Backup every Day & it goes to Tape everyday and Integrity check on every sunday night
I resotre todays' morning backup also & It also have same problem
Exactly I have no idea of which day backup is correct , & for to check the clean backup , we have to restore one by one all our last 7 days Backup
regards
ghanshyam
August 23, 2010 at 7:26 am
gupta1282 (8/23/2010)
Extent (6:5008984) in database ID 51 is marked allocated in the GAM, but no SGAM or IAM has allocated it.CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'KM_Test'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (KM_Test).
That's not the full output. There's at least 1 row missing from the end. Please post the entire output.
That was with the database in single user mode and CheckDB running with the repair_allow_data_loss option? It should have put a line in about whether or not it could repair each error.
Exactly I have no idea of which day backup is correct , & for to check the clean backup , we have to restore one by one all our last 7 days Backup
Yes, you will. It may be the only way of repairing this. How long do you retain log backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 8:19 am
Hi
Ok I will do it
Could u help me out in one more thing that from following error messgare
Extent (6:5008984) which table or index is using this page number ?
the output of DBCC Page(51,6,5008984,3) WITH TABLERESULTS is already is shared
if it possible , then we can track particular table and will create new table and dump all data
because if it is not important table , then we can ignore this
becoz taking backup from tape is very time consuming & although we are taking log backup every 10 min but there may be some log backup missing.
plz suggest
regards
ghanshyam
August 23, 2010 at 8:25 am
It's not that simple. There are three allocation structures that track page usage. This extent (group of 8 pages) is marked as allocated in one but not in the others. The DBCC Page output shows that it is not allocated to a table, therefore there's nothing you can just drop to fix this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2010 at 8:52 am
Hi
When I again execute following command
DBCC CheckDB('KM_Others_Testing') with No_Infomsgs , All_errormsgs
it's executed successfully without showing any error message
& according to u , it is not pointing to any table , means there is no data loss after executing it ?
regards
ghanshyam
August 23, 2010 at 8:59 am
One mistake was there
I executed that query
DBCC CheckDB('KM_Others_Testing', repair_allow_data_loss) with No_Infomsgs , All_errormsgs
And it executed successfully without any error
regards
ghanshyam
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply