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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's what I said to you to do, in my first comment (ID: 42428946)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VijayAuthor Commented:
Thank you very much Virot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Chat / IM

From novice to tech pro — start learning today.