SQL 2014: CheckDB issue

Question

Scenario: SQL Server 2014 : Simple Recovery with Full backup + Differentials (16TB)

I had to move 2 of the 3 data files to their drives with more space.

Got the following Checkdb errrors:

Msg 8904, Level 16, State 1, Line 1
Extent (3:330729680) in database ID 7 is allocated by more than one allocation object.

Msg 8904, Level 16, State 1, Line 1
Extent (3:330761496) in database ID 7 is allocated by more than one allocation object.

Msg 8904, Level 16, State 1, Line 1
Extent (3:330761952) in database ID 7 is allocated by more than one allocation object.

Msg 8905, Level 16, State 1, Line 1
Extent (4:69263216) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:112202728) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:183126192) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223416024) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223469792) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223471712) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223478376) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223500240) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223503600) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.


Msg 8905, Level 16, State 1, Line 1
Extent (4:223516488) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223519616) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223541992) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223546336) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223553376) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223566624) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223621032) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223631192) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223644072) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223648872) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223656320) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223669376) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223673368) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223686968) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223703600) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223711440) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223792128) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223839320) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223868168) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223878312) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223887808) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.


Msg 8905, Level 16, State 1, Line 1
Extent (4:223896816) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.


Msg 8905, Level 16, State 1, Line 1
Extent (4:223908672) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:223911288) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224431616) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224436752) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224439432) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224441488) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224482016) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224501120) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224518192) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224523144) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224523152) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224551560) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224552808) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224553264) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224560344) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224589640) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224599976) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224600000) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224605000) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224611080) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224632192) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224634448) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224634744) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224637504) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224639424) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224642672) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224650144) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

Msg 8905, Level 16, State 1, Line 1
Extent (4:224718072) in database ID 7 is marked allocated in the GAM, but no 
SGAM or IAM has allocated it.

CHECKDB found 62 allocation errors and 0 consistency errors not associated with any single object.


Msg 8913, Level 16, State 3, Line 1
Extent (3:330729680) is allocated to 'dbo.QTR2' and at least one other object.

Msg 8913, Level 16, State 3, Line 1
Extent (3:330761496) is allocated to 'dbo.QTR2' and at least one other object.

Msg 8913, Level 16, State 3, Line 1
Extent (3:330761952) is allocated to 'dbo.QTR2' and at least one other object.

CHECKDB found 3 allocation errors and 0 consistency errors in table 'dbo.QTR2' (object ID 1835731013).

CHECKDB found 65 allocation errors and 0 consistency errors in database 'DataStore'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DataStore).

Am currently exporting the data from the dbo.QTR2 table to another server.

I would like your input as it how to proceed to resolve these errors and specifically with allocation errors.

To add- the errorlogs had lot of stack dump text being written.

Update 1

**********************

Ran the following to find the suspect pages:

SELECT * FROM [msdb].[dbo].[suspect_pages];

Results: Null

Using one of the extents:

    DBCC TRACEON (3604); 
    DBCC PAGE (7, 4, 224650144, 0); 
    DBCC TRACEOFF (3604); 
    GO

Results:


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (4:224650144)


BUFFER:


BUF @0x00000049D1A4F0C0

bpage = 0x0000001864370000          bhash = 0x0000000000000000          bpageno = (4:224650144)
bdbid = 7                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 14680                       bstat = 0x9
blog = 0x5215215a                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x0000001864370000

m_pageId = (4:224650144)            m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 18567 m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057595254734848                                Metadata: PartitionId = 0
Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (4:155433890)
m_nextPage = (4:224650145)          pminlen = 30                        m_slotCnt = 231
m_freeCnt = 11                      m_freeData = 7719                   m_reservedCnt = 0
m_lsn = (2320287:6895221:8)         m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0                   m_tornBits = -1594742432            DB Frag ID = 1

Allocation Status

GAM (4:224430848) = ALLOCATED       SGAM (4:224430849) = NOT ALLOCATED  
PFS (4:224644200) = 0x40 ALLOCATED   0_PCT_FULL                          DIFF (4:224430854) = NOT CHANGED
ML (4:224430855) = NOT MIN_LOGGED   


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

the messages indicate the minimum repair involves data loss - that is almost NEVER any good unless you simply do not have any good backups. You should restore using the latest full and most recent diff since you're in simple mode. — Scott Hodgin - Retired, Aug 9, 2016 at 15:09

Ed Leighton-Dick · Accepted Answer · 2016-08-09 20:51:13Z

1

Use DBCC PAGE to investigate whether the cited extents are part of a nonclustered index, part of a clustered index, or something else. If they're part of a nonclustered index, you're in luck - the nonclustered index can be rebuilt without data loss. If the extents are part of either the clustered index (which contains the data itself) or part of something else (likely a system table), your best option is to restore to the last known good backup.

answered Aug 9, 2016 at 20:51

Ed Leighton-Dick

3481 silver badge4 bronze badges

I ran the DBCC checktable and it returned 0 allocation errors and and 27 consistency errors. I have 1 weeks of data which in unrecoverable and I am unable to take backup with_continue on error. Was wodnering can I drop the table that is causing this issue.
– rodrick
Aug 11, 2016 at 5:38
Possibly, but since it's corrupt, there aren't any guarantees. It depends on exactly where the corruption is.
– Ed Leighton-Dick
Aug 13, 2016 at 2:04

Add a comment |

Tara Kizer · Accepted Answer · 2016-08-09 21:03:06Z

1

Due to the size of the database, I would recommend doing page restores instead of restoring the entire database. Query the suspect_pages system table to get the list of pages that are corrupt. Then use the PAGE option of RESTORE DATABASE command which will get the non-corrupted pages from your backups and replace the corrupted ones in your database, that's assuming your backups aren't of a corrupted database. For more information, see here and here.

answered Aug 9, 2016 at 21:03

Tara Kizer

5,79614 silver badges28 bronze badges

I ran the following to find the suspect_pages: The results were null. <br/> <pre><code> SELECT * FROM [msdb].[dbo].[suspect_pages]; <div>DBCC TRACEON (3604); DBCC PAGE (7, 4, 224650144, 0); DBCC TRACEOFF (3604); GO </div> <br/> </code></pre>
– rodrick
Aug 9, 2016 at 21:57

Add a comment |

Stack Exchange Network

current community

your communities

more stack exchange communities

SQL 2014: CheckDB issue

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server sql-server-2014 dbcc-checkdb or ask your own question.

Hot Network Questions

SQL 2014: CheckDB issue

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server sql-server-2014 dbcc-checkdb or ask your own question.

Related

Hot Network Questions