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’)]

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.

Purpose: The purpose of this job is to monitor and gather statistics on databases with lower activity levels, especially focusing on those with less than 1% activity. It identifies these underutilized databases based on DML activity and generates historical and aggregate data for analysis.
Why: Monitoring the least used databases is essential to optimize resources, identify unused databases, and improve the overall performance of the server. These insights can help in capacity planning and cost-efficient management of database environments.
Manual checking: To manually check the database usage information, you can query the database using SQL commands related to activity metrics:

SELECT cntr_name, cntr_value_inactive, cntr_value_active FROM dbw_db_not_in_use_collector_aggr_histr;

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

Creating tables such as dbw_db_not_in_use_collector for tracking real-time database usage.
Historical data storage implementation in dbw_db_not_in_use_collector_histr and dbw_db_not_in_use_collector_aggr_histr.
A stored procedure dbw_db_not_in_use_collector_proc is implemented to manage the collection, update, and cleanup of usage data, based on specified job parameters.

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

The main procedure, dbw_db_not_in_use_collector_proc, checks and updates data relating to database activity and stores aggregate data for reporting.
It adjusts the data to reflect accurate usage metrics, including session counts and inactivity durations.

The job also includes configurations for report templates that break down databases by usage metrics, such as average inactivity, session count, and logged data flushing, helping with visualization and deeper analysis through the provided presentations.

This job depends on system tables (like master.dbo.sysprocesses), historical tables, and multiple views for pulling and validating data. Each dependency ensures the comprehensive capturing of data for less active databases.

Includes upgrades from previous versions by modifying procedures and ensuring that new and more useful parameters are calculated and included, like additional databases with low usage metrics beyond the 1% activity threshold.