DBCC UPDATEUSAGE

Job details

Name:	DBCC UPDATEUSAGE
Platform:	Sqlserver
Category:	Maintenance
Premium package:	Maintenance package
Description:	This task is performing the DBCC UPDATEUSAGE operation to corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
Long description:	Do not run DBCC UPDATEUSAGE routinely unless you suspect incorrect values are being returned by sp_spaceused or when getting error number 2508 from DBCC CHECKDB procedure.
Version:	2
Default schedule:	0 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’]/.[eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
ignore databases	msdb	List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
disable during working hours	YES	This parameter disables the alert to be scheduled, or to be run manually, during working hours (between 7am and 5pm).
max elap time	180	The maximum number of minutes before the dbWatch task stops running DBCC UPDATEUSAGE command for the remaining databases.
continue on next run	YES	If set to “YES“ (default) the DBCC CHECKDB command will continue where it left off.
include READ ONLY databases	NO	If set to “YES“ all databases with updateability property value “READ_ONLY“ will be included when performing the DBCC CHECKDB operation.
include Stand-by databases	NO	If set to “YES“ all databases with “IsInStandBy” property value “1″ will be included when performing the DBCC CHECKDB operation.
include Published databases	NO	If set to “YES“ all databases with “IsMergePublished” and “IsPublished “properties values “1″ will be included when performing the DBCC CHECKDB operation.
include Subscribed databases	NO	If set to “YES“ all databases with “IsSubscribed” property value “1″ will be included when performing the DBCC CHECKDB operation.

Job Summary

Purpose: The purpose of this job is to perform the DBCC UPDATEUSAGE operation, which is designed to correct any inaccuracies in the page and row counts in the catalog views of SQL Server databases. This correction helps ensure accurate report generation, especially through the sp_spaceused system stored procedure which is used to estimate database space usage.
Why: This job is important to maintain accuracy in database system reports that administrators rely on for performance tuning and storage allocation. Incorrect data can lead to inefficient resource use and possible database performance degradation. Regular execution of this job can prevent these scenarios.
Manual checking: You can check this manually in the database by issuing these SQL commands:

DBCC UPDATEUSAGE

Job Details

Specially tailored for instances running Microsoft SQL Server or Azure SQL Managed Instance with major version later than 2005.
It includes various configurations and parameters that can be customized such as ignoring specific databases, disabling execution during business hours, setting maximum elapsed time for execution, and handling of read-only, standby, and published/subscribed databases.
Dependency checks and clean-up operations are configured to maintain consistency and recovery in case of task failures.
Complements other database maintenance routines by providing detailed reports and history management.

Deployment and Implementation

Provides a detailed implementation script which includes procedures for running the update usage, handling parameters, and managing ignore lists for databases.
History and error reporting are embedded to gather execution metrics and identify issues during task runs.
Advanced settings allow administrators to adjust the execution according to specific database properties such as user access, updateability, and publishing status.

Output and Reporting

Tables detailing the task’s operation which includes:

DB count	Start Time	End Time	Elapsed Time	Error Count	Last Database Checked

select db_count "DB count", start_time "Start time", end_time "End time", ...
* Performance and error records are tabulated and made available for review to monitor success rates and troubleshoot problems.
* Specific presentations for historical data, detailed database checks, and error analysis are provided as part of the task's comprehensive reporting capabilities.

In essence, the monitoring job for dbWatch Control Center enables effective database maintenance by automating the DBCC UPDATEUSAGE process, ensuring data integrity, and providing robust reports and controls to handle diverse database environments efficiently.