I have a database that I have inherited which has been left for a long time to grow and grow. 95% of this growth is caused by 1 log table used by our application. The database is 120GB and in full recovery mode. The compressed backups are around 10GB. The server is 2008. The log file is 3.5GB
After asking I have found that I can remove all data from this log table over 3 months old. This gives me 100GB of free space. I have also written an agent job to keep the log table at this level.
This means I will have roughly 100GB sat in this file forever (as long as no other table starts growing in way it hasn't previously or the agent job fails)
I would like to free some of this space up while leaving enough room for future growth which should be much less then it was before my agent job was put in.
So I don't want to shrink and then defrag indexes as this may require a lot of downtime and is almost always a bad idea.
Is dropping the database and restoring the 10GB backup an option? I'm not even sure if this will give me the desired result which is a smaller database with something like 2GB free in it's file for future growth.