SQLMonitor on SQL Express - how to handle "database full" issue?

HugoKornelis

Hi all!

One of my customers has SQL Monitor installed on a SQL Server Express instance. This morning I noticed that data collection was not working properly. There were no clear messages in the SQL Monitor browser as to why.

Upon investigation, I eventually noticed that the error log of the host instance was filling up with "insufficiennt space" mesages. Apparently they had hit the 10GB limit for Express.

I went into the Data Purging page of the Configuration, changed most of the options to do a bit more agressive purging, then tried to save - and the response was a screen with HTTP Error 50.0 - Internal Server Error. And a load of other bullcrap but no clue as to the rooot cause (which I assume is that the program tries to write something to the DB *before* starting the purge process)


My questions:
1. Is there any way to purge some of the data so that the SQL Monitor instance becomes functional again?
2. Would the team consider future ennhancements that would provide better feedback on the root cause of the various issues? Both the non-working data collection and the failure to save the new data purge settings provided no helpful explanation at all.

HugoKornelis

I managed to get this solved myself. I noticed that there were windows of time where monitoring would temporarily resume so I figured that it does still purge data and then re-fill the free space. So I kept trying until today I managed to sneak in an update of the purge settings without getting an error.

Russell D

Hi Hugo,

If you look in the [RedGateMonitor].[settings].[PurgeSettings] table, this shows you your data retention periods if not using the defaults, which it looks like you're not. So you should be able to manually run an update to this table to lower them even further. At which point restarting the BaseMonitor should kick these in, and then its best to allow Monitor to work on this automatically.

Please bear in mind that the number returned by querying the above table will give you a position on the menu items array position. So for example:


to set the Basic Machine data to 1 day, you'd need to update the table to reflect 0:



We don't recommend using SQL Express as a repository database for this reason (https://documentation.red-gate.com/sm8/getting-started/requirements/supported-platforms). Unfortunately it fills up pretty quickly.

I'm not really sure we can improve upon this particular issue in terms of feedback. The problem exists because the database is full - in order to raise an alert on this Monitor would need to be able to write to the database. 

HugoKornelis

Thanks for the reply, Russell!

Unfortunately, the PurgeSettings table for this customer is totally empty, which I assume is how you store "use factury default settings". And I obviously cannot add a row to it.
I'll probably have to do some manual purging. Can you point me to some tables that I can safely trim down a bit?

The reason this customer decided to use Express edition is the phrasing on this page: https://documentation.red-gate.com/sm7/getting-started/requirements/hardware-and-performance-guidelines - I now found other pages where you are more explicit about not using Express but this page can defintely be interpreted as "you can use it, just purge a bit more agressive".
(They are monitoring 11 instances across 9 servers).

I'll talk to them about upgrading the instance to Standard Edition but my first priority now is to remove some data, restart the service. change the data purge settings, and then ensure that everything is working as it should once more. After that I'll try to find business cases for moving back to longer data retention.