One of the best practices from Microsoft for SQL Server is a database integrity check. Command DBCC CHECKDB means "Database Consistency Check" and this command checks the physical and logical integrity of all the objects in the databases, if these are not breached database structure.
DBCC CHECKDB includes the next three DBCC commands below:
• DBCC CHECKCATALOG
• DBCC CHECKALLOC
• DBCC CHECKTABLE
1) DBCC CHECKCATALOG - It is used to control the system tables in database.
Example : DBCC CHECKCATALOG ('Pigeon')
2) DBCC CHECKALLOC - This command checks coherence data pages with indexes.
Example : - DBCC CHECKALLOC ('Pigeon', REPAIR_FAST)
- DBCC CHECKALLOC ('Pigeon')
3) DBCC CHECKTABLE - This we can use to repair each table.
Example : - DBCC CHECKTABLE ('Pigeon', REPAIR_REBUILD)
- DBCC CHECKTABLE ('Pigeon')
When I summarize it all what it checks DBCC CHECKDB:
- Coherence Data Pages and indexes
- Data in each table are current
- Consistency database
- Index Views
This command should be performed regularly at least once a week.
Here is an article how I implemented DBCC CHECKDB on a weekly basis!
See you soon!