1 time shrink of abused databse or restore from backup [duplicate]

Question

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.

Thanks

Answer 1

Restoring your backup will in fact bring you right back to where you were when the backup was taken. This is actually one of the few cases where a shrink is appropriate. Fortunately it's a background process and shouldn't affect your system to much unless you are running a really heavy OLTP system. Once the shrink is done, and if I was you I should shrink down about 80GB and keep the other 20 for growth, you will almost certainly have to reindex. The reindex (and the fragmentation that requires it) will most likely cause you performance problems so you should probably consider doing the whole process during a maintenance window. If you don't have a maintenance window then try a slow period. Either way if you want to get rid of that extra space shrink is the only option you have that I know of.