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
- 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;
Details of Implementation
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.
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
- 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.
Report Generation
- 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.
Dependencies
- 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.
Upgrade Path
- 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.
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.