Would daily/weekly db memory cache clearing be of use?
Would clearing the database memory cache on a daily/weekly schedule be of use?


The straight answer is No. This would be a very bad idea, I suggest you don't do this.


  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


You probably need to understand how SQL Server memory works. SQL Server caches as much data pages as possible to avoid physical I/O, which of course is costly. If you clear memory used by database somehow, next day when it has to read pages it would have to bring it back into memory forcing I/O, perhaps lots of I/O on a busy system. Please note reading of database pages is always done in memory. I would also question your assumption about being sure that pages which are read today won't be, in any case, required tomorrow. This is not a correct idea.

It does seems like SQL Server is holding memory so how would it cater to new requests? This is simple, if your request needs pages which are already in memory, read would be done in memory. If the page is not there in memory I/O would be issued to bring it in memory and if space is not there in memory, least used pages would be flushed out to the disk to create space. This happens many a times on busy system without affecting performance.

It's costly for SQL Server to keep on taking and releasing memory because this process involves CPU and other resources; so SQL Server limits these by grabbing as much as it can in cache. 


  long term would be better to look a reducing size of database/indexes


If you take my advice and considering you don't have unused indexes, it would be better to increase RAM on system and provide more to SQL Server. Yes, of course your database design must be of a standard which does not have redundant indexes, and properly sized data and log files with an appropriate autogrowth value.