Search
InstantDoc ID
GO

Browse By: Author | Issue

November 19, 2009 08:19 PM

DBCC CHECKDB for Very Large Databases

Use the Admin/Worker Job approach
Rating: (0)
David Paul Giroux
SQL Server Magazine
InstantDoc ID #102873
DBCC CHECKDB is the T-SQL command that checks the logical and physical integrity of all the objects in a specified database. Most DBAs probably don’t think twice about running DBCC CHECKDB regularly—until their databases start to get very large. As the size of your database increases, you’ll encounter various challenges in running DBCC CHECKDB. For example, the time it takes to complete a full DBCC CHECKDB process might become prohibitive. In addition, there might not be enough data space for the snapshot created during the DBCC CHECKDB process.

Bes...

...This article is for paid Professional Members only.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Professional Membership

Monthly

Annual

VIP Membership

Monthly

Annual

Compare Member Benefits

Add a Comment

Hi Robbert,
I passed your question on to David Paul Giroux; his response is below:

"You are correct, running DBCC CHECKALLOC, CHECKTABLE, and/or CHECKCATALOG will not update the Value for the Field “dbi_dbccLastKnownGood” from DBCC INFO. In our environment we have DBCC scheduled every night in some capacity so we do not refer to the output from DBCC INFO. However, there are a couple of things you can do. If you really want to use that field then I would add “DBCC CHECKDB() WITH ESTIMATEONLY, NO_INFOMSGS” as the last step in the job. The command will only run for about a second on even very large databases. It took 1 second on a 1.5TB database that I tested it on. Even though the command is only producing some estimate values – it actually updates the value for dbi_dbccLastKnownGood.

That would be the simplest and you would not need to make further changes to your current system. Alternatively, you could create a table and update it every time you perform a non DBCC CHECKDB command. You could also check the default trace using a query similar as follows:


SELECT TextData,
StartTime,
DatabaseName
FROM sys.fn_trace_gettable((select [path] from sys.traces where is_default = 1) , default)
WHERE TextData LIKE 'DBCC%'
ORDER BY StartTime desc
GO


Of course, that data will eventually “roll over.”

Hope this helps."


Please feel free to contact me directly at mkeller@sqlmag.com if you have any more questions.

Thanks!

Megan Keller
Associate Editor, SQL Server Magazine

Megan1/29/2010 3:01:50 PM

Hi David,

I want to implement this procedure in our environment, but I ran against this issue. We check all the databases on the "Last Known Good Checkdb" available through DBCC DBINFO. Your procedure does not update this field. How do you check regulary that a checkdb has occurred?

thanks, Robbert

Robbert12/17/2009 3:24:24 AM

You must log on before posting a comment.

Are you a new visitor? Register Here
GOOGLE LINKS
SPONSORED LINKS
FEATURED LINKS

© 2010 Penton Media, Inc.

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.