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…