« Update für SQL Server 2014 (Dezember 2014)
Passwort gleich Login »

Die Reinheit der Daten

Das ist mal wieder ein Artikel, der auch mit "Es war einmal" anfangen könnte. Es war einmal vor dem Jahre 2005...

... da wurde mit DBCC noch nicht so viel kontrolliert wie heute. Mit der Version SQL Server 2005 wurde eine neue Option für DBCC CHECKDB eingeführt: DATA_PURITY.

Aus der Online-Doku zu DATA_PURITY

Bewirkt, dass DBCC CHECKDB die Datenbank auf Spaltenwerte überprüft, die ungültig sind oder außerhalb des zulässigen Bereichs liegen. So können mit DBCC CHECKDB z. B. Spalten mit Datums- und Zeitwerten festgestellt werden, die außerhalb des zulässigen Bereichs für den datetime-Datentyp liegen, oder es werden decimal-Spalten oder Spalten mit einem ungefähren numerischen Datentyp festgestellt, die ungültige Dezimal- oder Genauigkeitswerte enthalten.

Bei Datenbanken, die in SQL Server 2005 erstellt werden, ist die Überprüfung der Spaltenwertintegrität standardmäßig aktiviert; die Option DATA_PURITY ist in diesem Fall nicht erforderlich. Bei Datenbanken, die von früheren Versionen von SQL Server aktualisiert wurden, ist die Spaltenwertprüfung nicht standardmäßig aktiviert. Die Aktivierung erfolgt erst, wenn DBCC CHECKDB WITH DATA_PURITY fehlerfrei für die Datenbank ausgeführt wurde. Danach wird die Spaltenwertintegrität standardmäßig von DBCC CHECKDB überprüft.

Schlussfolgerungen

Da steht also, dass dies jetzt Standard ist, aber für ältere Datenbanken einmalig ausgeführt werden muss. Die Wartungspläne prüfen zwar mit DBCC CHECKDB, aber nicht mit der Option DATA_PURITY, da diese ja jetzt Standard ist. Wie finde ich also die "älteren Datenbanken" und welche Ausnahmen gibt es noch? Wer denkt, ich habe nur neue Datenbanken, die nach 2005 angelegt wurden und kann jetzt aufhören zu lesen, irrt sich leider gewaltig.

In den Katalogsichten gibt es erst mal keinen Hinweis darauf, wann eine Datenbank wirklich erstellt wurde. Das Create_Date bezieht sich eher auf einen Restore oder Attach-Vorgang in der aktuellen Instanz. Die Daten stehen gut versteckt im Kopf der Datenbanken und können nur mit undokumentierten Befehlen sichtbar gemacht werden. Hierzu gehört auch das Trace-Flag 3604, welches schon häufiger bei insidesql.org verwendet wurde.

Ausnahme 1: Ältere Datenbanken

In Anlehnung an diesen Artikel gibt es hier ein kleines Skript, welches alle Datenbanken einer Instanz untersucht und als Ergebnis die Datenbanken ohne die DATA_PURITY-Check Option sowohl in einer Liste ausgibt, als auch direkt die Befehle zur Korrektur.

DBCC TRACEON (3604);
GO
CREATE TABLE #DBCC (
      ParentObject VARCHAR(255),
      [Object] VARCHAR(255),
      Field VARCHAR(255),
      [Value] VARCHAR(255)
);

CREATE TABLE #DBCC2 (
     DatabaseName VARCHAR(255),
     ParentObject VARCHAR(255),
     [Object] VARCHAR(255),
     Field VARCHAR(255),
     [Value] VARCHAR(255)
);

EXEC MASTER.dbo.sp_MSFOREACHDB
'USE [?] INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS'');
INSERT INTO #DBCC2 SELECT ''?'', * FROM #DBCC;
DELETE FROM #DBCC;';
SELECT a.DatabaseName, a.Field, a.Value AS DATA_PURITY_Flag, b.Field , b.Value DB_Version
FROM #DBCC2 a
LEFT JOIN #DBCC2 b
ON a.DatabaseName = b.DatabaseName
WHERE a.Field = 'dbi_DBCCFlags'
AND b.Field = 'dbi_createVersion'
AND a.Value = '0'
ORDER BY a.DatabaseName;

SELECT 'DBCC CHECKDB ([' + a.DatabaseName + ']) WITH DATA_PURITY, NO_INFOMSGS;' AS Statement
FROM #DBCC2 a
LEFT JOIN #DBCC2 b
ON a.DatabaseName = b.DatabaseName
WHERE a.Field = 'dbi_DBCCFlags'
AND b.Field = 'dbi_createVersion'
AND a.Value = '0'
ORDER BY a.DatabaseName;
GO
DROP TABLE #DBCC;
DROP TABLE #DBCC2;
GO

Die Informationen im Feld "dbi_DBCCFlags" geben an, ob die neue Prüfung verwendet werden soll. Ist der Wert 0, wird sie nicht verwendet. Das Feld "dbi_createVersion" gibt uns die Version an, in der die Datenbank erzeugt wurde. Eine Liste der Versionen findet sich z. B. hier: http://sqlserverbuilds.blogspot.de/2014/01/sql-server-internal-database-versions.html.

Ausnahme 2: master und model

Anscheinend nicht gewollt und kaum zu glauben: Die Datenbanken master und model haben bei dbi_DBCCFlags den Wert 0, werden also nicht mit DATA_PURITY kontrolliert, bis wir dies aktivieren. It's a bug not a Feature! Paul Randall hat dies in seinem blog beschrieben und anscheinend auch schon direkt an das Entwicklungsteam um Bob Ward gemeldet. (Es war einmal 2013 ...)

Unabhängig von der Kontrolle oben, sollten wir also für jede Neuinstallation dieses berücksichtigen, bis der Bug behoben ist.

-- Für master und model einmalig nach Installation ausführen:
DBCC CHECKDB (N'master') WITH DATA_PURITY, NO_INFOMSGS;
DBCC CHECKDB (N'model') WITH DATA_PURITY, NO_INFOMSGS;
GO

  data_purity_flag.sql

administration, dbcc, trace flag
SQL Server
 Print article This entry was posted by cmu on 18.12.14 at 10:27:00 . Follow any responses to this post through RSS 2.0.