DBCC VARIOUS OPTIONS
————-Find BAD PAGES with 823 or 824 error—————
USE msdb
GO
SELECT * FROM dbo.suspect_pages
———DBCC TSQL ————————–
dbcc checkdb WITH PHYSICAL_ONLY, ALL_ERRORMSGS, NO_INFOMSGS
———Progress of DBCC CheckDB—————-
SELECT session_id ,
request_id , percent_complete , estimated_completion_time ,
DATEADD(ms,estimated_completion_time,GETDATE()) AS EstimatedEndTime,
start_time , status ,command
FROM sys.dm_exec_requests
WHERE database_id = ‘XX’ — Spacify DB ID here
—————————————————
———–When was last DBCC CHECKDB Run————-
DECLARE @name VARCHAR(256)</code>
CREATE TABLE #dbinfo
(ParentObject varchar(100),
Object varchar(100),
Field varchar(100),
Value varchar(100))
CREATE TABLE #dbinforesults
(dbname varchar(256),
LastRanDate datetime,
Status varchar(100),
DataPurityCheckEnabled varchar(3))
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state_desc=’ONLINE’
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dbinfo
EXEC(‘DBCC DBINFO ([‘[email protected]+’]) WITH TABLERESULTS’)
INSERT INTO #dbinforesults (dbname, LastRanDate, Status)
(SELECT DISTINCT @name as dbname, Value as LastRanDate,
CASE
WHEN Value = ‘1900-01-01 00:00:00.000’ THEN CAST(‘NeverRan’ AS VARCHAR)
WHEN DATEDIFF(d, Value, GETDATE()) > 14 THEN CAST(‘NotCurrent’ AS VARCHAR)
ELSE CAST(‘Current’ AS VARCHAR)
END AS Status
FROM #dbinfo
WHERE Field=’dbi_dbccLastKnownGood’)
UPDATE #dbinforesults SET DataPurityCheckEnabled=(SELECT
CASE
WHEN @name=’master’ OR @name=’model’ THEN ‘N/A’
WHEN Value=0 THEN ‘No’
WHEN Value=2 THEN ‘Yes’
END as DataPurityEnabled
FROM #dbinfo
WHERE Field=’dbi_dbccFlags’)
WHERE [email protected]
TRUNCATE TABLE #dbinfo
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
DROP TABLE #dbinfo
SELECT * FROM #dbinforesults
DROP TABLE #dbinforesults
————————Multiple options for CHECK DB ——————–
A. Check the integrity of all user databases
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’
B. Check the physical integrity of all user databases
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@PhysicalOnly = ‘Y’
C. Check the integrity of all user databases, using the option not to check nonclustered indexes
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@NoIndex = ‘Y’
D. Check the integrity of all user databases, using the option to perform extended logical checks
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKDB’,
@ExtendedLogicalChecks = ‘Y’
E. Check the integrity of the filegroup PRIMARY in the database AdventureWorks
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘AdventureWorks.PRIMARY’
F. Check the integrity of all filegroups except the filegroup PRIMARY in the database AdventureWorks
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKFILEGROUP’,
@FileGroups = ‘ALL_FILEGROUPS, -AdventureWorks.PRIMARY’
G. Check the integrity of the table Production.Product in the database AdventureWorks
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘AdventureWorks’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘AdventureWorks.Production.Product’
H. Check the integrity of all tables except the table Production.Product in the database AdventureWorks
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKTABLE’,
@Objects = ‘ALL_OBJECTS, -AdventureWorks.Production.Product’
I. Check the disk-space allocation structures of all user databases
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKALLOC’
J. Check the catalog consistency of all user databases
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = ‘USER_DATABASES’,
@CheckCommands = ‘CHECKCATALOG’