When trying to add a new SQL database for monitoring with SQL Performance Investigator an incorrect "Insufficient Resources" message displayed when installing SQL PI repository”.
Message is incorrect as the environment has the necessary resources to meet requirements according to the Databases Deployment Guide.
A possible cause for this behavior is the incorrect removal of a database instance that is being monitored:
By design SQL Performance Investigator (SQL PI) data cannot be manually removed from the SQL PI repository and needs to wait for the default retention time of two years before it is purged. This behavior is relevant to the data stored in the repository, but should not affect the monitoring status of the database instance in relation with the SQL Performance Investigator extension.
There may be situations, for example when a database agent is not properly removed, when database instances are still marked as monitored on SQL PI even when they are no longer visible from the Databases Global View dashboard in the Foglight Web Interface.
For a SQL Server database, the monitored status can be verified from the "pass_instance_dim" table on the SQL PI repository. The following steps should be followed:
The flag "monitored" column in pass_instance_dim is the determining factor to count the number of active instances in PI Repository. This flag should be set to '0' for databases that are no longer being monitored which should stop the insufficient resources warning, as the deactivated database would no longer be counted.
The following update query can be run to disable the 'monitored' status:
update pass_instance_dim set monitored = 0 where instance_key in (...);
Note: The "instance_key" can be collected from the output of "select * from pass_instance_dim;"