Skip to content

Instantly share code, notes, and snippets.

@wqweto

wqweto/gist:4257321

Created December 11, 2012 09:34
Show Gist options
  • Save wqweto/4257321 to your computer and use it in GitHub Desktop.
Save wqweto/4257321 to your computer and use it in GitHub Desktop.
Download ZIP
Fix for 'Last good DBCC CHECKDB over 2 weeks old'
Raw
gistfile1.sql
/*
Sample DBCC DBInfo() With TableResults output. Notice double dbi_dbccLastKnownGood field. Fix at the bottom.
ParentObject Object Field VALUE
------------------------------- --------------------------------- ------------------------------------ ---------------------------------------------------------
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbid 5
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_status 9502720
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_nextid 348222265
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbname Dreem15_IVB
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_maxDbTimestamp 8759200
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_version 661
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_createVersion 539
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_ESVersion 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_nextseqnum 1900-01-01 00:00:00.000
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_crdate 2010-01-22 14:07:40.643
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_filegeneration 0
DBINFO @0x00000000420BD670 dbi_checkptLSN m_fSeqNo 325620
DBINFO @0x00000000420BD670 dbi_checkptLSN m_blockOffset 423
DBINFO @0x00000000420BD670 dbi_checkptLSN m_slotId 114
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_RebuildLogs 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbccFlags 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbccLastKnownGood 2012-12-11 01:30:01.357
DBINFO @0x00000000420BD670 dbi_dbbackupLSN m_fSeqNo 325583
DBINFO @0x00000000420BD670 dbi_dbbackupLSN m_blockOffset 16769
DBINFO @0x00000000420BD670 dbi_dbbackupLSN m_slotId 1
DBINFO @0x00000000420BD670 dbi_oldestBackupXactLSN m_fSeqNo 325583
DBINFO @0x00000000420BD670 dbi_oldestBackupXactLSN m_blockOffset 16769
DBINFO @0x00000000420BD670 dbi_oldestBackupXactLSN m_slotId 1
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_LastLogBackupTime 2012-12-11 10:00:00.890
DBINFO @0x00000000420BD670 dbi_differentialBaseLSN m_fSeqNo 325578
DBINFO @0x00000000420BD670 dbi_differentialBaseLSN m_blockOffset 62
DBINFO @0x00000000420BD670 dbi_differentialBaseLSN m_slotId 39
DBINFO @0x00000000420BD670 dbi_createIndexLSN m_fSeqNo 0
DBINFO @0x00000000420BD670 dbi_createIndexLSN m_blockOffset 0
DBINFO @0x00000000420BD670 dbi_createIndexLSN m_slotId 0
DBINFO @0x00000000420BD670 dbi_versionChangeLSN m_fSeqNo 266207
DBINFO @0x00000000420BD670 dbi_versionChangeLSN m_blockOffset 270
DBINFO @0x00000000420BD670 dbi_versionChangeLSN m_slotId 40
DBINFO @0x00000000420BD670 dbi_MinSkipLSN m_fSeqNo 0
DBINFO @0x00000000420BD670 dbi_MinSkipLSN m_blockOffset 0
DBINFO @0x00000000420BD670 dbi_MinSkipLSN m_slotId 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_familyGUID 520f9292-de9f-45d8-871a-f4aa21265b18
dbi_recoveryForkNameStack entry 0 m_fSeqNo 158660
dbi_recoveryForkNameStack entry 0 m_blockOffset 214
dbi_recoveryForkNameStack entry 0 m_slotId 1
DBINFO @0x00000000420BD670 dbi_recoveryForkNameStack m_guid 11d89463-b0d1-4761-be45-ff1f1fbea38c
dbi_recoveryForkNameStack entry 1 m_fSeqNo 158660
dbi_recoveryForkNameStack entry 1 m_blockOffset 214
dbi_recoveryForkNameStack entry 1 m_slotId 1
DBINFO @0x00000000420BD670 dbi_recoveryForkNameStack m_guid 2bff44c6-a94d-414d-84ec-1cfe22aefafd
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_differentialBaseGuid 06594378-e45e-4078-948d-2652925981a4
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_firstSysIndexes 0001:00000010
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_collation 53269
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_category 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_maxLogSpaceUsed 18446744073709551615
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_localState 11
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_roleSequence 2
DBINFO @0x00000000420BD670 dbi_failoverLsn m_fSeqNo 325630
DBINFO @0x00000000420BD670 dbi_failoverLsn m_blockOffset 1372
DBINFO @0x00000000420BD670 dbi_failoverLsn m_slotId 1
DBINFO @0x00000000420BD670 dbi_dbmRedoLsn m_fSeqNo 325620
DBINFO @0x00000000420BD670 dbi_dbmRedoLsn m_blockOffset 423
DBINFO @0x00000000420BD670 dbi_dbmRedoLsn m_slotId 114
DBINFO @0x00000000420BD670 dbi_dbmOldestXactLsn m_fSeqNo 325620
DBINFO @0x00000000420BD670 dbi_dbmOldestXactLsn m_blockOffset 423
DBINFO @0x00000000420BD670 dbi_dbmOldestXactLsn m_slotId 114
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbMirrorId ad8dba58-ec1a-44d0-b3e6-3a2671ba0a28
DBINFO @0x00000000420BD670 dbi_pageUndoLsn m_fSeqNo 0
DBINFO @0x00000000420BD670 dbi_pageUndoLsn m_blockOffset 0
DBINFO @0x00000000420BD670 dbi_pageUndoLsn m_slotId 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_disabledSequence 77
DBINFO @0x00000000420BD670 dbi_dvSplitPoint m_fSeqNo 0
DBINFO @0x00000000420BD670 dbi_dvSplitPoint m_blockOffset 0
DBINFO @0x00000000420BD670 dbi_dvSplitPoint m_slotId 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_CloneCpuCount 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_CloneMemorySize 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_updSysCatalog 1900-01-01 00:00:00.000
DBINFO @0x00000000420BD670 dbi_LogBackupChainOrigin m_fSeqNo 270722
DBINFO @0x00000000420BD670 dbi_LogBackupChainOrigin m_blockOffset 7092
DBINFO @0x00000000420BD670 dbi_LogBackupChainOrigin m_slotId 89
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbccLastKnownGood 2012-12-11 01:30:01.357
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_modDate 1900-01-01 00:00:00.000
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_verPriv 171052960
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_svcBrokerGUID 859a3bb7-b78f-450a-8512-3a4527d8de4d
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_svcBrokerOptions 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbmLogZeroOutstanding 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbmLastGoodRoleSequence 1
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbmRedoQueue 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_rmidRegistryValueDeleted 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_dbmConnectionTimeout 10
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_fragmentId 0
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_AuIdNext 1099512062968
DBINFO STRUCTURE: DBINFO @0x00000000420BD670 dbi_CurrentGeneration 0
dbi_EncryptionHistory Scan 0 m_fSeqNo 0
dbi_EncryptionHistory Scan 0 m_blockOffset 0
dbi_EncryptionHistory Scan 0 m_slotId 0
DBINFO @0x00000000420BD670 dbi_EncryptionHistory EncryptionScanInfo:ScanId 0
dbi_EncryptionHistory Scan 1 m_fSeqNo 0
dbi_EncryptionHistory Scan 1 m_blockOffset 0
dbi_EncryptionHistory Scan 1 m_slotId 0
DBINFO @0x00000000420BD670 dbi_EncryptionHistory EncryptionScanInfo:ScanId 0
dbi_EncryptionHistory Scan 2 m_fSeqNo 0
dbi_EncryptionHistory Scan 2 m_blockOffset 0
dbi_EncryptionHistory Scan 2 m_slotId 0
DBINFO @0x00000000420BD670 dbi_EncryptionHistory EncryptionScanInfo:ScanId 0
(100 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
--- this works
IF OBJECT_ID('tempdb..#DBs') IS NOT NULL DROP TABLE #DBs
GO
CREATE TABLE #DBs
(
Id INT IDENTITY(1, 1)
PRIMARY KEY ,
ParentObject VARCHAR(255) ,
Object VARCHAR(255) ,
Field VARCHAR(255) ,
Value VARCHAR(255) ,
DbName SYSNAME NULL
)
EXEC sp_MSforeachdb N'USE [?];
INSERT #DBs(ParentObject, Object, Field, Value)
EXEC (''DBCC DBInfo() With TableResults, NO_INFOMSGS'');
UPDATE #DBs SET DbName = N''?'' WHERE DbName IS NULL;';
WITH
DB2
AS ( SELECT DISTINCT Field ,
Value ,
DbName
FROM #DBs
WHERE Field = 'dbi_dbccLastKnownGood'
)
--INSERT INTO #BlitzResults
-- ( CheckID ,
-- Priority ,
-- FindingsGroup ,
-- Finding ,
-- URL ,
-- Details
-- )
SELECT 68 AS CheckID ,
50 AS PRIORITY ,
'Reliability' AS FindingsGroup ,
'Last good DBCC CHECKDB over 2 weeks old' AS Finding ,
'http://BrentOzar.com/go/checkdb' AS URL ,
'Database [' + DB2.DbName + ']'
+ CASE DB2.Value
WHEN '1900-01-01 00:00:00.000'
THEN ' never had a successful DBCC CHECKDB.'
ELSE ' last had a successful DBCC CHECKDB run on '
+ DB2.Value + '.'
END
+ ' This check should be run regularly to catch any database corruption as soon as possible.'
+ ' Note: you can restore a backup of a busy production database to a test server and run DBCC CHECKDB '
+ ' against that to minimize impact. If you do that, you can ignore this warning.' AS Details
FROM DB2
WHERE CAST(DB2.Value AS DATETIME) < DATEADD(DD, -14, CURRENT_TIMESTAMP)
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment