Periodically, you should check repository integrity to prevent problems such as uncontrolled database growth, corruption of database tables, or inconsistencies in database structure.
As with other database maintenance tasks, the frequency of database consistency checking depends on several factors, including the number of jobs you run, the number of events you generate, the number of data points you collect, and the stability of network communication. In general, the larger the repository, the more frequently you should check database consistency.
Several SQL Server dbcc commands can be used to check table consistency, segment usage, page allocation, pointer operations, and index operations. You can also use the AMAdmin_DBHealth Knowledge Script to perform a more limited check of the syslog system table and the main tables that store AppManager activity, such as the tables for events, data, and jobs.
NOTE:The AMAdmin_DBHealth Knowledge Script also checks the percentage of data space and log space used by the QDB, the time it takes a query to execute, and the status of scheduled SQL Server jobs. For more information about using this Knowledge Script, see the Knowledge Script Help.
The following dbcc commands can be executed in any SQL Server query tool, such as SQL Server Management Studio or isql. For more information about the syntax to use with these commands, see the Microsoft Transact-SQL Reference Guide.
|
Command |
Description |
|---|---|
DBCC CHECKCATALOG |
Checks the system tables for consistency and display segment information. This command is highly effective and typically takes less time to complete than other consistency-checking commands. |
DBCC NEWALLOC |
Ensures that page allocation is correct and that the page structure for the data and index pages is consistent. NOTE:Run this command in single-user mode. |
DBCC TEXTALL |
Checks text and image allocation errors for all tables that contain text and image columns. |
DBCC CHECKTABLE |
Ensures that all pointers and data and index pages in a specified table are consistent and properly linked. |
DBCC CHECKDB |
Ensures that all pointers and data and index pages for all tables in the database are consistent and properly linked. |
Because most database consistency checks can take several hours to run, you should plan to run these processes during off-peak hours. You should also include these checks in your overall database maintenance plan and backup strategy to ensure you always back up a clean database. For example, if you are nearing maximum capacity on a server, you should check the database consistency in preparation for backing up.
The AM Self Monitoring module, also known as AM Health, includes the AMHealth_QDBComponentsHealth Knowledge Script for monitoring the health and availability of SQL Server resources for the QDB. For more information, see the module management guide on the AppManager Modules Documentation page.