asked on

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.

Rich Weissler

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.)

Vijay

ASKER

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.

Vitor Montalvão

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.

Vijay

ASKER

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.

Vitor Montalvão

Did you use any of the REPAIR options?

Bharat Bhushan

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:

You can try a text comparison tool to check the difference between original and corrupted file
RUN DBCC CHEKCDB command to check the database consistency
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/

Vijay

ASKER

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.

Vitor Montalvão

They i used DBCC CHECKTABLE and repaired all the corrupted tables.

Can you provide the complete command that you've used?

Vijay

ASKER

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

Vitor Montalvão

You didn't repair nothing. Just checked the tables integrities.
A database is more than a collection of tables.

Vijay

ASKER

Hi Vitro,

After running above script, i found few tables got corrupted.
Then i used DBCC CHECTABLE ('table', Allow_data_loss) for repairing.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vijay

ASKER

Thank you very much Virot.