Database Corruption Week 6 Solution
http://stevestedman.com/server-health/database-corruption-challenge/week-6-database-corruption-challenge/week-6-challenge-details/
Check for corruption in the database, isolate to non clustered index.
Keep changes that will be lost when repairing damage. In this case there are 2 row first name values that “look” to be incorrect in the cluster. Write two simple updates to be run following disable and rebuild of non clustered index – cross checking with business that the values are indeed corrupt. Then run final check script.
USE [master]
RESTORE DATABASE [CorruptionChallenge6] FROM DISK =
N'C:\SQL_Data\CorruptionChallenge6.bak' WITH FILE = 1, NOUNLOAD,
STATS = 5,REPLACE
GO
USE [CorruptionChallenge6]
GO
;WITH
c AS(
SELECT [id],[FirstName],[LastName] FROM [dbo].[Customers] WITH
(INDEX([PK_Customers]))
),
nc AS(
SELECT [id],[FirstName],[LastName] FROM [dbo].[Customers] WITH
(INDEX([ncFName]))
)
SELECT * FROM c FULL JOIN nc ON c.[id] = nc.[id] WHERE c.[FirstName] <> nc.[FirstName] OR c.[LastName] <> nc.[LastName]
--id FirstName LastName id FirstName LastName
--252971 €ea MULLEN 252971 Mia MULLEN
--992 Aliwander FISCHER 992 Alexander FISCHER
GO
ALTER INDEX [ncFName] ON [dbo].[Customers] DISABLE
GO
ALTER INDEX [ncFName] ON [dbo].[Customers] REBUILD
GO
DBCC CHECKDB('CorruptionChallenge6') WITH NO_INFOMSGS,ALL_ERRORMSGS
GO
SELECT COUNT(1) AS check1,
COUNT(DISTINCT FirstName) AS check2,
COUNT(DISTINCT MiddleName) AS check3,
COUNT(DISTINCT LastName) AS check4,
CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
CHECKSUM_AGG(CHECKSUM(LastName)) AS check7 FROM [dbo].[Customers];
--check1 check2 check3 check4 check5 check6 check7
--254031 19 17 879 -8736600 179 1330080960
SELECT * FROM [dbo].[Customers] WHERE [id] IN (992,252971)--Aliwander/€ea
GO
UPDATE [dbo].[Customers] SET [FirstName] = 'Mia' WHERE [id] = 252971
UPDATE [dbo].[Customers] SET [FirstName] = 'Alexander' WHERE [id] = 992
GO
SELECT COUNT(1) AS check1,
COUNT(DISTINCT FirstName) AS check2,
COUNT(DISTINCT MiddleName) AS check3,
COUNT(DISTINCT LastName) AS check4,
CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
CHECKSUM_AGG(CHECKSUM(LastName)) AS check7 FROM [dbo].[Customers];
--check1 check2 check3 check4 check5 check6 check7
--254031 17 17 879 1046491532 179 1330080960
SELECT * FROM [dbo].[Customers] WHERE [id] IN (992,252971)
Now the problem is that corruption has been corrected with what we think should be in the clustered index, based on a hunch that the data looks better. The page verify option should have caught this corruption as it is set to page checksum.
But this corruption also rewrote the page checksum ;-p
--Alternative Fix for corruption in clustered index, then fix nc as before. DO NOT USE DBCC WRITEPAGE in PRODUCTION environments!!! It is very dangerous.
/*
--FOR TEST ENVIRONMENTS ONLY--
DBCC PAGE ([CorruptionChallenge6],1,174,3) WITH TABLERESULTS
DBCC PAGE ([CorruptionChallenge6],1,1215,3) WITH TABLERESULTS
ALTER DATABASE [CorruptionChallenge6] SET SINGLE_USER
DBCC WRITEPAGE (N'CorruptionChallenge6',1,174,896,2,0x6578,0);--Byte 1426304-1426305 69 77 to 65 78
DBCC WRITEPAGE (N'CorruptionChallenge6',1,1215,3488,2,0x4D69,0);--Byte 9956768-9956769 80 65 to 4D 69
ALTER DATABASE [CorruptionChallenge6] SET MULTI_USER
*/