Thursday, May 19, 2011

How often checkdb should be run against database?

I would recommend to run CHECKDB against database once in a day in the midnight.

As part of regular database maintenance job i scheduled to run checks the logical and physical integrity of all the objects in the specified database.

when you run dbcc checkdb performing the following opearation:

Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
eg:
-- Check the AdventureDb database.
DBCC CHECKDB (AdventureDb)
GO

--Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.
USE AdventureWorks
GO
DBCC CHECKDB WITH PHYSICAL_ONLY

-- Check the AdventureWorks database without nonclustered indexes.
DBCC CHECKDB (AdventureWorks, NOINDEX);
GO

No comments: