So memory use is hitting limits. Would clearing the database memory cache on a daily/weekly schedule be of use?
(Ignoring the fact I probably should be looking at reducing size of database/indexes)
Bunch of SQL Server database (all on one SQL Server instance), one server, and some memory. Tons of data is written to database, but most of this is just relevant to the current day, its a monitoring system thing. So over a couple of days, the cache will be eaten up by info of the previous day, which may or may not be needed/viewed by anyone for some time, or ever.
So as it writes data, and checks what's in database, it compares against data of the current day. Once the day rolls over, it not comparing against that data any more.
So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed? Would this be suitable case of FreeSystemCache/DropCleanBuffers
And yes - long term would be better to look a reducing size of database/indexes, however that also falls into what the data being stored is for, which actually makes archiving of data somewhat tricky without some interlinked program logic.