Databases NOT IN USE collector


Job details

Name: Databases NOT IN USE collector
Platform: Sqlserver
Category: Capacity
Description: Collects information about most inactive databases.
Long description: This dbWatch task collects information relating to the least used databases. It gathers information from all databases showing a usage rate of less than 1%,
Version: 2
Default schedule: 0 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘1999′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name Default value Description
keep data for 365 Number of days to provide statistics for.
max additional databases 100 In addition to databases with less than 1% activity, statistics for additional databases, with the lowest usage rates (but higher than 1%), will be collected. This parameter controls the number of these databases for which statistics are to be collected.
max databases per graph 5 The maximum number of databases visualized in the report graphs.

Job Summary

SELECT cntr_name, cntr_value_inactive, cntr_value_active FROM dbw_db_not_in_use_collector_aggr_histr;

Details of Implementation

The job involves several SQL procedures and table creation statements to gather and aggregate data about database usage:

Tables Created

Table Name Description
dbw_db_not_in_use_collector Stores real-time database usage data
dbw_db_not_in_use_collector_histr Maintains history of inactive and active database usage statistics
dbw_db_not_in_use_collector_aggr_histr Aggregates data for historical and analytical review

SQL Procedure Overview

Report Generation

Dependencies

Upgrade Path

Use within dbWatch Control Center
This job, tailored for systems like SQL Server, will allow database administrators to maintain an efficient and cost-effective environment by focusing on databases that are possibly over-allocated resources relative to their usage, aiding in decisions for scaling, maintenance, or decommissioning.