1

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

Improve this question
3
  • The compressed backup will expand back to your 120GB uncompressed size.
    – billinkc
    Commented Jan 21, 2014 at 13:19
  • what kind of database, oracle? what version?
    – kevinskio
    Commented Jan 21, 2014 at 13:37
  • I'm guessing by "The server is 2008" he means SQL Server 2008.
    – Colin 't Hart
    Commented Jan 21, 2014 at 13:41

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • Thanks. OK that makes sense. So I'll run the shrink and then sort out the index fragmentation. Should I defrag the physical data file?
    – James Anderson
    Commented Jan 21, 2014 at 14:28
  • I probably wouldn't but to be fair I have absolutely no idea of the effect one way or the other. Nor have I ever read about it one way or the other.
    – Kenneth Fisher
    Commented Jan 21, 2014 at 14:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.