SQL Server Database Corruption and Impact of running CHECKDB repair with allow data loss | Times of server

SQL Server Database Corruption and Impact of running CHECKDB repair with allow data loss

Issue

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?

Arrangement

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

information misfortune.

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.

Utilize ace

GO

DROP DATABASE IF EXISTS [CorruptionTest]

GO

Make DATABASE [CorruptionTest]

GO

Adjust DATABASE [CorruptionTest] MODIFY FILE ( NAME = N’CorruptionTest’, SIZE = 2GB )

GO

Adjust DATABASE [CorruptionTest] MODIFY FILE ( NAME = N’CorruptionTest_log’, SIZE = 2GB )

GO

Adjust DATABASE [CorruptionTest] SET RECOVERY FULL;

GO

Adjust DATABASE [CorruptionTest] SET PAGE_VERIFY CHECKSUM

GO

Make TABLE CorruptionTest.dbo.mssqltips

(increase INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(2000) DEFAULT ‘a’,

File CIX_SQLShack_increment1 UNIQUE CLUSTERED (increase))

GO

SET NOCOUNT ON;

Pronounce @counter INT = 1;

Start TRAN

WHILE @counter <= 250000

Start

Embed INTO CorruptionTest.dbo.mssqltips (increase, randomGUID, randomValue)

Qualities (@counter, NEWID(), ABS(CHECKSUM(NewId())) % 140000000)

SET @counter += 1

END;

Submit TRAN;

GO

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

sys.fn_PhysLocFormatter(%%physloc%%) PageId,

*

FROM [CorruptionTest].[dbo].[mssqltips]

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

GO

DBCC PAGE (‘CorruptionTest’, 1, 226, 3);

GO

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 *

FROM [CorruptionTest].[dbo].mssqltips

GO

SELECT TOP 249994 *

FROM [CorruptionTest].[dbo].mssqltips

Request BY increase DESC

GO

SELECT TOP 249995 *

FROM [CorruptionTest].[dbo].mssqltips

Request BY increase DESC

GO

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.&#xA;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.

Utilize ace;

Change DATABASE [CorruptionTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

DBCC CHECKDB (‘CorruptionTest’, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

GO

Change DATABASE [CorruptionTest] SET MULTI_USER;

GO

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

End

In this tip, we saw that SQL Server expels the entire information page that contains a solitary undermined

push.

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.

Following stages

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

<script>

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami