I am finding out about SQL Server database defilement and had an inquiry. At the point when a solitary line in
a SQL Server information page is ruined, for instance due to
page CHECKSUM mistake, what will happen on the off chance that I run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS?
Would I lose more information or is just the ruined column deallocated?
Contingent upon the kind of defilement, information misfortune utilizing CHECKDB with REPAIR_ALLOW_DATA_LOSS
can shift to various degrees. In particular, to the issue portrayal, notwithstanding when
we know without a doubt just a solitary line is defiled in an information page which causes a CHECKSUM
blunder, the entire information page will be deallocated when SQL Server repairs with permit
CHECKSUM is ascertained and put away at the page level (not at each line level) henceforth
it gives an abnormal state of information record respectability. Here is an over-rearrangements of how page
CHECKSUM works; expect an information page contain 100 columns with a solitary estimation of 2 in each
push. The CHECKSUM calculation wholes these qualities and stores the aggregate (which is 200) to the
page header as a checksum. An outer factor outside of SQL Server then by one means or another
transforms one of the lines into an estimation of 0 in the information page. At the point when SQL Server peruses
this information page into the cushion pool and approves the page checksum, the computed
checksum esteem would be 198, yet the checksum esteem put away in the page header would
still be 200. Henceforth, SQL Server would raise this as a CHECKSUM debasement mistake, on the grounds that
the real and expected checksum esteems are extraordinary.
The exhibit underneath will step you through an invented case of a page CHECKSUM
blunder and you will see that SQL Server deallocates the entire information page notwithstanding when
just a solitary line is tainted.
SQL Server Corruption Demonstration
The code show here is composed on SQL Server 2016 Developer Edition on
Administration Pack 2.
This exhibit will center around what information are disposed of when REPAIR_ALLOW_DATA_LOSS
choice is determined with DBCC CHECKDB.
Make a Corrupt Test SQL Server Database
The content beneath will make a database [CorruptionTest] and populate table dbo.mssqltips
with 250,000 columns. Table dbo.mssqltips has a section BigCol with information compose CHAR(2000).
With this, we are anticipating that every datum page should fit 3 lines for each page.
DROP DATABASE IF EXISTS [CorruptionTest]
Make DATABASE [CorruptionTest]
Adjust DATABASE [CorruptionTest] MODIFY FILE ( NAME = N’CorruptionTest’, SIZE = 2GB )
Adjust DATABASE [CorruptionTest] MODIFY FILE ( NAME = N’CorruptionTest_log’, SIZE = 2GB )
Adjust DATABASE [CorruptionTest] SET RECOVERY FULL;
Adjust DATABASE [CorruptionTest] SET PAGE_VERIFY CHECKSUM
Make TABLE CorruptionTest.dbo.mssqltips
(increase INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(2000) DEFAULT ‘a’,
File CIX_SQLShack_increment1 UNIQUE CLUSTERED (increase))
SET NOCOUNT ON;
Pronounce @counter INT = 1;
WHILE @counter <= 250000
Embed INTO CorruptionTest.dbo.mssqltips (increase, randomGUID, randomValue)
Qualities (@counter, NEWID(), ABS(CHECKSUM(NewId())) % 140000000)
SET @counter += 1
The inquiry beneath yields the table dbo.mssqltips top 10 pushes crosswise over 3 information pages
– Page Id 224, 226 and 227. Of course, we can see every datum page contains 3 columns.
For this debasement exhibit, we haphazardly pick push with increase = 5 in
blue to work with. We will degenerate the incentive in section BigCol in Page Id 226 Slot
1 with 0x0.
In this tip, we won’t depict the means to degenerate an incentive in an information page.
You can utilize the T-SQL order in my past tip
Undetected SQL Server Data Corruption with Database Page Verify choice set to NONE
to make the debasement.
SELECT TOP 10
Inquiry to recover top 10 lines from table
Examine the Corrupt Data Page Content in SQL Server
In this way, we have adulterated the incentive in BigCol with 0x0 on Page Id 226 Slot 1. Utilizing
DBCC PAGE, we can take a gander at the substance of Page Id 226.
From the yield, Slot 0 and Slot 2 in Page Id 226 is as yet conveying all the esteem
that we have embedded. The estimation of BigCol in Slot 1 is conveying “clear”,
since we have overwritten it with esteem 0x0.
DBCC TRACEON (3604);
DBCC PAGE (‘CorruptionTest’, 1, 226, 3);
DBCC PAGE to show substance of undermined page id 226
Getting to Corrupted Data Page in SQL Server
Debasement can stay covered up in a database except if the ruined page is perused
by capacity motor into memory and cushion pool approves the page checksum, or the
debasement is recognized when playing out a database consistency check.
To demonstrate this announcement above, we will execute 3 questions. The initial two questions
won’t get to the degenerate page. In any case, the third inquiry will hit the degenerate
information page with SELECT TOP 24995.
SELECT TOP 3 *
SELECT TOP 249994 *
Request BY increase DESC
SELECT TOP 249995 *
Request BY increase DESC
The initial 2 inquiries execute effectively and return lines since it needn’t bother with
to contact the undermined information page. The third question comes up short since it requires getting to
the tainted page. When the capacity motor endeavors to recover the tainted
information page, the inquiry was ended and restores a blunder.
3 questions demonstrating mistake just when capacity motor needs to get to the adulterated information page.
The blunder message show that the normal and real checksum esteem is extraordinary.
DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS
We should repair the database with permit information misfortune. We should set the
database into single client mode, run the repair and after that set the database once more into
multi client mode.
Change DATABASE [CorruptionTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (‘CorruptionTest’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
Change DATABASE [CorruptionTest] SET MULTI_USER;
Repair database with permit information misfortune
We run the underlying inquiry to recover the best 10 pushes again and it would seem that
increase 4, 5 and 6 are currently gone. This is on account of the entire Page Id 226 was deallocated.
All lines in table dbo.mssqltips get another Page Id in light of the fact that the table has been remade
furthermore, distributed new pages. In the event that you read the DBCC CHECKDB yield messages firmly, all
these points of interest were portrayed in the messages sheet above.
Question to recover top 10 lines from table
In this tip, we saw that SQL Server expels the entire information page that contains a solitary undermined
Likewise, if debasement happens on framework tables, it would mean client table sections
are deallocated from framework tables despite the fact that the client table itself isn’t
degenerate. Henceforth, after the repair with permit information misfortune the client tables influenced
would simply vanish.
Settling debasement is a fragile procedure and your odds to keep away from
debasement are better with a thorough reinforcement administration and intermittent reestablish
testing of reinforcement documents, and additionally running trustworthiness checks.
Last Update: 2018-08-24
next webcast catch
next tip catch
About the creator
MSSQLTips creator Simon Liew
Simon Liew is an autonomous SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.
View every one of my tips