Datenbanken reparieren mit CheckDB – Teil 2

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.

Hier ein kleines Beispiel:

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.

Beispiel: Die Beschädigte Datenbank

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.

Mainzer Datenfabrik - Datenbanken reparieren mit CheckDB – Teil 2

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

Auf die beschädige Datenseite zugreifen

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:

Reparieren der Datenbank

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

Datenbanken reparieren mit CheckDB – Teil 2

Hier ein kleines Beispiel:

Beispiel: Die Beschädigte Datenbank

Auf die beschädige Datenseite zugreifen

Reparieren der Datenbank

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.