Page corruptioin in SQL Server 2008 R2

How to resolve this page corruption:

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

PAGE: (53759:2092630070)


BUFFER:


BUF @0x0000000BA2FFE700

bpage = 0x0000000BA2F9C000           bhash = 0x0000000000000000           bpageno = (3:7106937)
bdbid = 5                            breferences = 3                      bUse1 = 26800
bstat = 0x6c00809                    blog = 0x15215999                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x0000000BA2F9C000

m_pageId = (53759:2092630070)        m_headerVersion = 54                 m_type = 0
m_typeFlagBits = 0xad                m_level = 124                        m_flagBits = 0xd1ff
m_objId (AllocUnitId.idObj) = 1129467990                                  m_indexId (AllocUnitId.idInd) = 0
Metadata: AllocUnitId = 74020814192640                                    Metadata: PartitionId = 0
Metadata: IndexId = -1               Metadata: ObjectId = 0               m_prevPage = (256:196608)
m_nextPage = (12596:1397507328)      pminlen = 8192                       m_slotCnt = 19777
m_freeCnt = 13104                    m_freeData = 12593                   m_reservedCnt = 0
m_lsn = (196608:536871168:17664)     m_xactReserved = 21324               m_xdesId = (20566:1296118068)
m_ghostRecCnt = 17234                m_tornBits = 825307952              

Allocation Status

GAM (3:6646016) = ALLOCATED          SGAM (3:6646017) = NOT ALLOCATED    
PFS (3:7101264) = 0x40 ALLOCATED   0_PCT_FULL                             DIFF (3:6646022) = NOT CHANGED
ML (3:6646023) = 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.
VijayAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's what I said to you to do, in my first comment (ID: 42428946)
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Do you have backups of the database?
I assume you don't have any fault tolerance or recovery on this database?
Which version of MS SQL are you using?

Do you already know why/how the page corrupted?  (And have that resolved?  That's important... if the page corrupted because of failures in the disk subsystem... and those failures are still occurring... well... this corrupt page is just the first of many.

I guess the third thing I'd want to do is figure out what is on that page.  (The first two were making certain you have good backups, and confirming you don't have a larger problem which is going to cause additional corruption.)  Assuming it's not a non-clustered index that can be fixed with an index rebuild, you best option will be to restore from backups.  (And if you're in full recovery mode and backup the tail of the log, you should be able to recover without data loss with good backups.)
0
 
VijayAuthor Commented:
This is SQL Server 2008 (SP2). This is part of windows cluster and subscriber (Transactional Replication)
We don't  have idea that why this DB was corrupted.
We can able to access this DB. We don't have any issue with this DB for accessibility, all application are working fine.
But when i check from suspect_page table from msdb. We found corrupted Page ID, then i use DBCC Page and provided output above.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to run the DBCC CHECKDB with one of the REPAIR options: { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }

IMPORTANT: Mind that REPAIR_ALLOW_DATA_LOSS can make you loss data.
0
 
VijayAuthor Commented:
When i run DBCC CHECKDB i am getting below error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you use any of the REPAIR options?
0
 
Bharat BhushanSolution ManagerCommented:
I am assuming that you do not have the updated backup of your database. To fix  the page level corruption, I will suggest you to try these:

  1. You can try a text comparison tool to check the difference between original and corrupted file
  2. RUN DBCC CHEKCDB command to check the database consistency
  3. Examine the infected page by DBCC PAGE command. Switch on the trace flag 3604 first

DBCC TRACEON (3604)

DBCC PAGE ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Open in new window


Read the complete blog here: https://www.stellarinfo.com/blog/how-to-fix-sql-database-page-level-corruption/
0
 
VijayAuthor Commented:
Already i mentioned that
When i run DBCC CHECKDB i am getting below error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Last Option like,
Finally i found T-SQL script, which will gothourgh each and every table and scan any report corrupted table.
They i used DBCC CHECKTABLE and repaired all the corrupted tables.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
They i used DBCC CHECKTABLE and repaired all the corrupted tables.
Can you provide the complete command that you've used?
0
 
VijayAuthor Commented:
use pubs
go
declare @tabname sysname 
declare @dbstring varchar(300) 
declare @exec_string varchar(300)
 
declare tabDBCC cursor for select table_name from information_schema.tables where 
table_type = 'base table'
 
open tabDBCC 
fetch next from tabDBCC into @tabname 
 
select @dbstring = DB_NAME()
print 'Starting DBCC CHECKTABLE for database ' + upper(@dbstring)
 
while (@@fetch_status = 0) 
begin 
select @exec_string = 'dbcc checktable(' + @tabname + ')' 
exec(@exec_string) 
fetch next from tabDBCC into @tabname 
end 
 
close tabDBCC 
deallocate tabDBCC
 

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You didn't repair nothing. Just checked the tables integrities.
A database is more than a collection of tables.
0
 
VijayAuthor Commented:
Hi Vitro,

After running above script,  i found few tables got corrupted.
Then i used DBCC CHECTABLE ('table', Allow_data_loss) for repairing.
0
 
VijayAuthor Commented:
Thank you very much Virot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.