Run database checks but omit large tables or filegroups - New option in Ola Hallengren's Scripts
- by Greg Low
One of the things I've always wanted in DBCC CHECKDB is the
option to omit particular tables from the check. The situation that I often see
is that companies with large databases often have only one or two very large
tables. They want to run a DBCC CHECKDB on the database to check everything
except those couple of tables due to time constraints.
I posted a request on the Connect site about time some time
ago:
https://connect.microsoft.com/SQLServer/feedback/details/611164/dbcc-checkdb-omit-tables-option
The workaround from the product team was that you could
script out the checks that you did want to carry out, rather than omitting the
ones that you didn't. I didn't overly like this as a workaround as clients
often had a very large number of objects that they did want to check and only
one or two that they didn't.
I've always been impressed with the work that our buddy Ola
Hallengren has done on his maintenance scripts. He pinged me recently about my
old Connect item and said he was going to implement something similar. The good
news is that it's available now.
Here are some examples he provided of the newly-supported
syntax:
EXECUTE
dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands =
'CHECKDB'
EXECUTE
dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands =
'CHECKALLOC,CHECKTABLE,CHECKCATALOG', @Objects =
'AdventureWorks.Person.Address'
EXECUTE
dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands =
'CHECKALLOC,CHECKTABLE,CHECKCATALOG', @Objects = 'ALL_OBJECTS,-AdventureWorks.Person.Address'
EXECUTE
dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands =
'CHECKFILEGROUP,CHECKCATALOG', @FileGroups = 'AdventureWorks.PRIMARY'
EXECUTE
dbo.DatabaseIntegrityCheck @Databases = 'AdventureWorks', @CheckCommands =
'CHECKFILEGROUP,CHECKCATALOG', @FileGroups = 'ALL_FILEGROUPS,-AdventureWorks.PRIMARY'
Note the syntax to omit an object from the list of objects
and the option to omit one filegroup.
Nice! Thanks Ola!
You'll find details here: http://ola.hallengren.com/
