Diligent DBAs like to check their databases for a little thing called corruption. Some of those DBAs are masterful at keeping records and logging that the database was checked and came up with a clean bill of health.

There are many different ways of logging this kind of activity. Today I will share one such way to track when the last successful run of Checkdb happened.

First a little back story

A question came across twitter on the SQLhelp hashtag. The question was “Does restoring a database clear the dbi_dbccLastKnownGood value on the boot page?”

This question prompted me to do a quick test to see. The test is simple. Create a small database, backup the database, run checkdb, check to see what the dbi_dbccLastKnownGood value is, restore the previous backup and check the dbi_dbccLastKnownGood value again.

So here is a quick script

[codesyntax lang=”tsql”]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

USE[master]

GO

IFEXISTS(SELECT name FROM sys.databases WHERE name=N'TestB')

DROP DATABASE[TestB]

GO

USE[master]

GO

CREATE DATABASE[TestB]

GO

DECLARE@BackupPath VARCHAR(256)

,@BackupName VARCHAR(50)

SET@BackupPath='C:\Database\Backup\' --replace with valid file path

SET @BackupName = 'TestB.bak'

SET @BackupPath = @BackupPath + @BackupName

BACKUP DATABASE [TestB]

TO DISK = @BackupPath

WITH init;

GO

CREATE TABLE #temp (

Id INT IDENTITY(1,1),

ParentObject VARCHAR(255),

[Object] VARCHAR(255),

Field VARCHAR(255),

[Value] VARCHAR(255)

)

INSERT INTO #temp

EXECUTE ('DBCC Page(TestB,1,9,3)WITH TABLERESULTS');

/* You will get two results from the following query */

SELECT *

FROM #temp

WHERE Field = 'dbi_dbccLastKnownGood';

/* TAKE note OF the date returned by the last query */

DROP TABLE #temp;

GO

DBCC CHECKDB(TestB) WITH no_infomsgs;

GO

CREATE TABLE #temp (

Id INT IDENTITY(1,1),

ParentObject VARCHAR(255),

[Object] VARCHAR(255),

Field VARCHAR(255),

[Value] VARCHAR(255)

)

INSERT INTO #temp

EXECUTE ('DBCC Page(TestB,1,9,3)WITH TABLERESULTS');

/* You will get two results from the following query */

SELECT *

FROM #temp

WHERE Field = 'dbi_dbccLastKnownGood';

/* TAKE note OF the date returned by the last query */

DROP TABLE #temp;

GO

/* Now Restore the database */

DECLARE @BackupPath VARCHAR(256)

,@BackupName VARCHAR(50)

SET @BackupPath = 'C:\Database\Backup\'--replace with valid file path

SET@BackupName='TestB.bak'

SET@BackupPath=@BackupPath+@BackupName

/* for this contrived example, i will not take a tail log backup

and just use replace instead */

RESTORE DATABASE TestB

FROM DISK=@BackupPath

WITH REPLACE;

GO

/* Rerun The Boot Page Check */

CREATE TABLE#temp (

Id INTIDENTITY(1,1),

ParentObject VARCHAR(255),

[Object]VARCHAR(255),

Field VARCHAR(255),

[Value]VARCHAR(255)

)

INSERT INTO#temp

EXECUTE('DBCC Page ( TestB,1,9,3) WITH TABLERESULTS');

/* You will get two results from the following query */

SELECT *

FROM#temp

WHERE Field='dbi_dbccLastKnownGood';

/* TAKE note OF the date returned by the last query */

DROP TABLE#temp;

GO

[/codesyntax]

If you run this little test, you should observe that the date value for dbi_dbccLastKnownGood changes with each test. Now let’s discuss the question and the answer to that original question about whether or not this value gets cleared.

The value does not get cleared. The value does not get reset. The cause for the change in the value that you have observed is due simply to the boot page having been restored. If CheckDB has never been run on the database, you will get the SQL default date of ‘1900-01-01 00:00:00.000’.

And then…

Now that the back story is told, that brings us to how to track this – at least one such method. I had to cover the back story since it is what prompted the writing of a method to gather this information in a quick script so I could use it to monitor. Yes, it is just another tool to throw into the toolbox. And to reiterate, it is by no means the only way to track or gather this type of information. Some are more elaborate than others. It is up to you to choose.

Keeping in mind that we can get the last time that Checkdb completed without a report of corruption, I delve into this cursor based method to retrieve the dbi_dbccLastKnownGood value for all databases within your SQL Server Instance.

You can use this or find something else if you desire. The sole purpose of this script is to provide a means to gather quickly the last date known to report a clean bill of health from Checkdb. From there, I will leave it to you to add it to another process in order to track over the long term.

You can read the twitter conversation here. I also have it on good authority that Bob Pusateri (twitter) will be blogging about the same topic.

Within the world of SQL Server there are a few things one can be certain of – things will change. This is true of the features in SQL Server. Additionally, Extended Events is constantly evolving which underscores this constant change.

What is this gaping hole in the coverage of Extended Events? To be honest, it is not a very complicated topic or very difficult gap to fill. It’s just something that has been overlooked. The gap boils down to this: how does one consistently find the correct path to the Extended Event Log file (XEL file)?

Legislation and regulation sometimes dictates that certain activities must be tracked within a database. On occasion, it will be required that queries be audited and tracked to reach compliance with the legislation or regulation. To achieve this compliance, this article will demonstrate how to use Extended Events to audit statements being executed within the database.