Top query search data & Data Retention

DonFerguson

Would somebody please explain how automatic data pruning is supposed to work with with "Top query search data"  stored in the configured ...\SearchEngine\SqlQueries folder of the base monitor?

I ask because when I look in my data retention settings configuration, it shows a grayed out "Retain for" value of "1 week", meaning I can't configure it.  However it shows the "Size on disk" as being 381.83 GB.   

When I total up the index files stored in the SqlQueries folder, the vast majority of the index files are well over 1 week old, leaving me to believe that no actual pruning of that folder is occurring.

So my question is, if no actual pruning is by SQL Monitor is taking place of that folder, would it be safe for me to purge files older than 1 week on my own without breaking the "Top queries search" functionality? 

Jon_Kirkwood

Hi @DonFerguson

 

Thank you for reaching out on the Redgate forums regarding your SQLQueries folder size question

 

The "C:\ProgramData\Red Gate\SQL Monitor\SearchEngine\SqlQueries" folder is for the functionality for searching the Top Queries on the server overviews.  The data retention period for those files is linked to the SQL top queries data

 

 

To change the retention of the files, you need to change the retention of the "SQL top queries data" entry above it. This should help to purge the older data.

 

Alternatively, it is possible to change the location of the SQLQueries folder such that it does not increase usage on C: drive

The following article has information on changing the location 

https://documentation.red-gate.com/sm/performance-and-activity-monitoring/dashboards-and-overviews/server-overview-pages/sql-server-overview/performance-diagnostics/top-queries/configuring-top-query-search

 

Another option is to disable the search feature.

This can be done by adding this feature flag as follows and then restart the base monitor service:

 

If it doesn't exist, create the file appsettings.json in the %ProgramData%\Red Gate\SQL Monitor folder and then add this text to it (if it doesn't exist) 

{
    "FeatureFlags":
    {
        "TopQuerySearch": 0
    }
}

or add a comma at the end of any existing feature flag line and put the "TopQuerySearch": 0 on the next line (if it does exist with another feature flag already in it) - and then restart the base monitor service.

 

Please let us know if this helps.

DonFerguson

Thank you for the response Jon; however my fundamental issue is that pruning of my already alternatively configured path (per https://documentation.red-gate.com/sm/performance-and-activity-monitoring/dashboards-and-overviews/server-overview-pages/sql-server-overview/performance-diagnostics/top-queries/configuring-top-query-search), does not appear to be functioning.  I am picking up that it's supposed to work, but when the vast majority of the timestamp of the files is well older than a week, it seems that it is not functioning as intended.  I guess I need to open a ticket to have it looked at, but I believe it's an issue in the product.