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’