THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Run database checks but omit large tables or filegroups - New option in Ola Hallengren's Scripts

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/

 

Published Monday, July 02, 2012 6:02 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gavin Morris said:

Hi Greg,

We have this problem - some of our customer databases have ~50GB of useful structured data and ~400GB of blobs. Can you point me at anything on the considerations for skipping the check on these tables?

July 9, 2012 8:46 PM
 

Greg Low said:

Hi Gavin,

That's exactly what Ola's options do. You can specify a list of tables to omit.

Regards,

Greg

July 9, 2012 9:19 PM
 

Mark Hions said:

Microsoft considers the workaround of multiple CHECKTABLE operations to be "non-onerous", which is a new term to add to my dictionary.

November 29, 2012 2:20 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement