question

datadude avatar image
datadude asked

Suspect Page but no error in DBBC CHECKDB

1) After changing backup with checksum option, I getting an error during backup: Msg 3043, Level 16, State 1, Line 1 BACKUP '8ManDB_Test' detected an error on page (1:1915001) in file 'E:\MSSQL12.MSSQL2014EE\MSSQL\DATA\8ManDB_Test.mdf'. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. Msg 3201, Level 16, State 2, Line 6 Cannot open backup device 'D:\MSSQL12.MSSQL2014EE\MSSQL\Backup\ph_test_8man.bak'. Operating system error 2(The system cannot find the file specified.). Msg 3013, Level 16, State 1, Line 6 VERIFY DATABASE is terminating abnormally. 2) When I run CHECKDB: DBCC CHECKDB([8ManDB_Test]) WITH NO_INFOMSGS, ALL_ERRORMSGS, I get no results 3) In msdb.dbo.supect_pages I found this: 51 1 1915001 3 9 2016-08-01 22:51:50.000 4) When I run DBCC Page DBCC TRACEON (3604) DBCC PAGE ('8ManDB_Test', 1, 1915001, 0) DBCC TRACEOFF (3604) I get this result: PAGE: (51:3145776) BUFFER: BUF @0x00000017112FCE40 bpage = 0x0000000D8B75C000 bhash = 0x0000000000000000 bpageno = (1:1915001) bdbid = 51 breferences = 1 bcputicks = 0 bsampleCount = 0 bUse1 = 45036 bstat = 0x809 blog = 0x2121215a bnext = 0x0000000000000000 PAGE HEADER: Page @0x0000000D8B75C000 m_pageId = (51:3145776) m_headerVersion = 48 m_type = 0 m_typeFlagBits = 0x30 m_level = 0 m_flagBits = 0x33 m_objId (AllocUnitId.idObj) = 1024 m_indexId (AllocUnitId.idInd) = 50 Metadata: AllocUnitId = 14073748902641664 Metadata: PartitionId = 0 Metadata: IndexId = -1 Metadata: ObjectId = 0 m_prevPage = (50432:310) m_nextPage = (0:1067310336) pminlen = 0 m_slotCnt = 0 m_freeCnt = 1 m_freeData = 40 m_reservedCnt = 48 m_lsn = (3145776:3276851:310) m_xactReserved = 0 m_xdesId = (53248:50432) m_ghostRecCnt = 16215 m_tornBits = 0 DB Frag ID = 1 Allocation Status GAM (1:1533696) = ALLOCATED SGAM (1:1533697) = ALLOCATED PFS (1:1908768) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:1533702) = NOT CHANGED ML (1:1533703) = NOT MIN_LOGGED 5) Page restore is not possible because the suspected page exists in old backups, too. 6) I changed Page Verify from CHECKSUM to NONE, but no changes. 7) I made a backup without checksum and restored it as a copy, so experimental ways a possible 8) I am using SQL Server 2014 EE 12.0.4100.1, but the database runs on compatibility level SQL Server 2008 (100) 9) Answer to JohnM: DBCC CHECKDB([8ManDB_Test]) WITH ALL_ERRORMSGS DBCC results for '8ManDB_Test'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. DBCC results for 'sys.sysrscols'. There are 41905 rows in 572 pages for object "sys.sysrscols". DBCC results for 'sys.sysrowsets'. There are 6497 rows in 84 pages for object "sys.sysrowsets". DBCC results for 'sys.sysclones'. There are 0 rows in 0 pages for object "sys.sysclones". ... About 9600 row like There are X row in x pages ... There are 20445 rows in 580 pages for object "fs._file_system_paths_129435". DBCC results for 'fs._file_system_problems_129607'. There are 0 rows in 0 pages for object "fs._file_system_problems_129607". DBCC results for 'fs._file_system_acls_129686'. There are 605 rows in 2 pages for object "fs._file_system_acls_129686". DBCC results for 'fs._file_system_shares_129404'. There are 4 rows in 1 pages for object "fs._file_system_shares_129404". DBCC results for 'fs._file_system_local_account_children_129636'. There are 8 rows in 1 pages for object "fs._file_system_local_account_children_129636". DBCC results for 'fs._file_system_shares_129363'. There are 5 rows in 1 pages for object "fs._file_system_shares_129363". DBCC results for '_butler_object_values'. There are 1164 rows in 25 pages for object "_butler_object_values". DBCC results for 'fs._file_system_changed_shares_129520'. There are 0 rows in 0 pages for object "fs._file_system_changed_shares_129520". DBCC results for 'fs._file_system_aces_129596'. There are 86 rows in 1 pages for object "fs._file_system_aces_129596". CHECKDB found 0 allocation errors and 0 consistency errors in database '8ManDB_Test'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Thanks in Advance for your help.
suspect
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Given that you didn't get any results, I'd suggest running CHECKDB again minus the NO_INFOMSGS and see what it returns. Without knowing what type of corruption it is it'll be difficult to troubleshoot.
1 Like 1 ·
What object does that page belong to? I'm wondering if it belongs to a non-clustered index that might have been rebuilt during the course of the night?
0 Likes 0 ·
Hi JohnM, I tried to read the dbcc page and I also read a few blogs specially Paul Randal, but when I look on my dbcc page, it makes no sense to me. I haven't find out if this is a data or index page and I can not found the relation to a table or an index. i.e. select * from sys.objects so where so.object_id = 1024 does not exists.
0 Likes 0 ·
Does this return anything? SELECT p.object_id,OBJECT_NAME(p.object_ID),* FROM sys.allocation_units au INNER JOIN sys.partitions p ON au.container_id = CASE au.type WHEN 2 THEN p.partition_id ELSE p.hobt_id END WHERE au.allocation_unit_id = 14073748902641664
0 Likes 0 ·
Hi JohnM, the result of your query is empty.
0 Likes 0 ·
Show more comments

1 Answer

·
jason_clark03 avatar image
jason_clark03 answered
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@jason_clark03: Thanks for your input, but I do not understand how the link could help me. Do you have any suggestions for me ?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.