In diesem Beitrag wollen wir uns noch einmal mit dem DBCC CHECKDB Kommando beschäftigen und untersuchen, wie sich REPAIR_ALLOW_DATA_LOSS im Falle einer einzelnen beschädigten Zeile verhält. Existiert beispielsweise aufgrund eines Seiten-CHECKSUM-Fehlers eine beschädigte Zeile in einer Datenbank und wir reparieren diese mit REPAIR_ALLOW_DATA_LOSS, würden wir hier nur die beschädigte Zeile, oder gar noch mehr Daten verlieren?
Der Datenverlust von CHECKDB mit REPAIR_ALLOW_DATA_LOSS kann, abhängig von der Art der Beschädigung, unterschiedlich stark variieren. Im Falle eines CHECKSUM-Fehlers, verursacht durch eine einzelne beschädigte Zeile, wird bei einer Reparatur, die Datenverlust zulässt, dennoch die gesamte Datenseite freigegeben.
CHECKSUM selbst wird auf Seitenebene und nicht auf Zeilenebene berechnet, was ein hohes Maß an Datendateien-Integrität gewährleistet.
Angenommen, eine Datenseite enthält 100 Zeilen mit einem einzelnen Wert von 2 in jeder Zeile. Der CHECKSUM-Algorithmus summiert diese Werte auf und speichert die Summe als Prüfsumme im Seitenkopf. Diese beträgt in diesem Beispiel 200. Nun könnte ein externer Faktor außerhalb von SQL Server den Wert einer der 100 Zeilen von 2 auf 0 setzen. Würde die Seite nun in den Pufferpool eingelesen und die Prüfsumme berechnet werden, so wäre diese 198. Der im Seitenkopf gespeicherte Prüfsummenwert würde jedoch weiterhin 200 betragen. SQL Server würde dies nun als eine CHECKSUM-Beschädigung erkennen, da die Prüfsummenwerte unterschiedlich sind.
Wir wollen nun ein praktisches Beispiel durchführen und zeigen, dass im Falle einer beschädigten Zeile tatsächlich die gesamte Datenseite freigegeben wird.
Als Erstes wollen wir für das Beispiel eine Datenbank erstellen und diese mit Werten füllen. Wir verwenden dafür ein SQL Skript, das uns eines Datenbank TestBase und eine Tabelle TestTable erstellt. Die Tabelle wird anschließend mit 250.000 Reihen befüllt. Innerhalb der Tabelle befindet sich eine Spalte BigCol mit dem Datentyp CHAR(2000). Pro Datenseite erwarten wir nun 3 Zeilen.
USE master
GO
DROP DATABASE IF EXISTS TestBase
GO
CREATE DATABASE TestBase
GO
ALTER DATABASE TestBase MODIFY FILE ( NAME = N'TestBase', SIZE = 2GB )
GO
ALTER DATABASE TestBase MODIFY FILE ( NAME = N'TestBase_log', SIZE = 2GB )
GO
ALTER DATABASE TestBase SET RECOVERY FULL;
GO
ALTER DATABASE TestBase SET PAGE_VERIFY CHECKSUM
GO
use Testbase
go
create Schema Testbase
go
CREATE TABLE TestBase.TestTable
(increment INT, randomGUID uniqueidentifier, randomValue INT, BigCol CHAR(2000) DEFAULT 'a',
INDEX CIX_SQLShack_increment1 UNIQUE CLUSTERED (increment))
GO
SET NOCOUNT ON;
DECLARE @counter INT = 1;
BEGIN TRAN
WHILE @counter <= 250000
BEGIN
INSERT INTO TestBase.TestTable (increment, randomGUID, randomValue)
VALUES (@counter, NEWID(), ABS(CHECKSUM(NewId())) % 140000000)
SET @counter += 1
END;
COMMIT TRAN;
GO
Mit der Abfrage
SELECT TOP 10
sys.fn_PhysLocFormatter(%%physloc%%) PageId,
*
FROM TestBase.TestTable
können wir uns nun über 3 Datenseiten die ersten 10 Zeilen ausgeben lassen. Und wie erwartet enthält jede Datenseite 3 Reihen.
Die Datenbank beschädigen
Um nun mithilfe dieser Datenbank und der erstellten Tabelle DBCC CHECKDB zu testen, müssen wir diese beschädigen. Hierfür beschädigen wir den Wert in BigCol an der Seiten ID 226 Slot 1 und setzen ihn 0.
Dies tun wir mithilfe dieses Kommandos:
USE master;
GO
ALTER DATABASE TestBase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC WRITEPAGE ('TestBase', 1, 8338, 1155, 1, 0x0, 1)
GO
ALTER DATABASE TestBase SET MULTI_USER;
GO
Um zu überprüfen, ob der Wert tatsächlich 0 gesetzt wurde, können wir die folgende Abfrage verwenden:
DBCC TRACEON (3604);
GO
DBCC PAGE ('TestBase', 1, 8338, 3);
GO
Beschädigungen innerhalb einer Datenbank können leicht unentdeckt bleiben. Durch Einlesen der beschädigten Seite durch die Speicher Engine wird die Seitenprüfsumme durch den Pufferpool überprüft, der so eine Beschädigung feststellen kann. Ebenfalls kann die Beschädigung durch eine Datenbankkonsistenz Prüfung erkannt werden.
Nun können wir das DBCC CHECKDB durchführen und die die Datenbank auf Beschädigungen überprüfen:
DBCC CHECKDB (TestBase)
Wir erhalten folgende Ausgabe und sehen, dass die Datenbank beschädigt ist:
Nun können wir mit der eigentlichen Reparatur der Datenbank beginnen. Hierfür setzen wir die Datenbank zuerst in den Single User Mode
ALTER DATABASE TestBase SET SINGLE_USER
führen anschließend REPAIR_ALLOW_DATA_LOSS durch
DBCC CHECKDB (N'Testbase', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS
GO
Nun müssen wir die Datenbank wieder auf Multi User Mode setzen:
ALTER DATABASE TestBase SET MULTI_USER
Wir führen nun erneut die Abfrage für die ersten 10 Zeilen durch und können sehen, dass die Zeilen mit increment 4, 5 und 6 verschwunden sind. Dies liegt daran, dass die gesamte Seiten ID 8338 freigegeben wurde. Alle Zeilen in der Tabelle TestTable erhalten eine neue Seiten ID, da die Tabelle neu erstellt und neue Seiten zugewiesen wurden.
SELECT TOP 10
sys.fn_PhysLocFormatter(%%physloc%%) PageId,
*
FROM TestBase.TestTable
Kontaktieren Sie uns gerne über das
Kontaktformular und vereinbaren ein unverbindliches
Beratungsgespräch mit unseren Berater:innen zur
Bedarfsevaluierung. Gemeinsam optimieren wir Ihre
Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!