This is a technique that I am using from Paul Randal – concept of splitting CHECKDB: CHECKALLOC, CHECKCATALOG and CHECKTABLE – here we will use “buckets” of tables split across the week.
Lets assume Adventureworks is a VLDB 🙂
Day 1 would be the below
USE AdventureWorks2012 GO DBCC CHECKALLOC GO DBCC CHECKCATALOG GO
Now lets take 1 bucket of tables for day 1 for CHECKTABLE.
SELECT ''+SCHEMA_NAME(schema_id)+'.'+name+''
AS TableName, NTILE(7)
OVER (ORDER BY Name)
AS [BUCKET] INTO ##CHECKTABLE
FROM sys.tables WHERE type = 'U'
DECLARE @TABLE NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)
DECLARE CHECKTABLE CURSOR FAST_FORWARD
FOR
SELECT TableName FROM ##CHECKTABLE
WHERE BUCKET = 1
OPEN CHECKTABLE
FETCH NEXT FROM CHECKTABLE INTO @TABLE
WHILE @@FETCH_STATUS=0
BEGIN
BEGIN TRY
SET @SQL = 'DBCC CHECKTABLE(''' + @TABLE + ''') '
PRINT @SQL
EXEC SP_EXECUTESQL @SQL
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (
@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
) WITH LOG;
END CATCH
FETCH NEXT FROM CHECKTABLE INTO @TABLE
end
CLOSE CHECKTABLE
DEALLOCATE CHECKTABLE
DBCC CHECKTABLE('Person.Address')
DBCC CHECKTABLE('Person.AddressType')
DBCC CHECKTABLE('dbo.AWBuildVersion')
DBCC CHECKTABLE('Production.BillOfMaterials')
DBCC CHECKTABLE('Person.BusinessEntity')
DBCC CHECKTABLE('Person.BusinessEntityAddress')
DBCC CHECKTABLE('Person.BusinessEntityContact')
DBCC CHECKTABLE('Person.ContactType')
DBCC CHECKTABLE('Person.CountryRegion')
DBCC CHECKTABLE('Sales.CountryRegionCurrency')
DBCC CHECKTABLE('Sales.CreditCard')
Then the 2nd day you could do the 2nd bucket. etc…
