Database is too large due to appended backup

Question

My database MDF file is at around 600mb. LDF file is at 147MB.

I seriously doubt that it should be anywhere near this.

I suspect it's because the database has been backed up by my host with the "Append to the existing backup set" option checked.

How can I get the database to a reduced size? My host has sent me the .bak file and I've restored it.

Also is there a way to check if the database is actually this big, i.e is in the data causing the large size?

Answer 1

I'm seriously doubting that it should be anywhere near this.

Run sp_spaceused to analyze the actual space in use in the MDF. Run DBCC LOGINFO to analyze the space used in the LDF. Measurement beats guess every single time.

I suspect it's because the db has been backed up by my host with the "Append to the existing backup set" option checked.

Absolutely unrelated.

how can i get the db to a reduced size?

First, measure it, see what is the required size. If the database is indeed largely empty, first you need to understand why it reached a larger size. Unless you have a clear explanation why it grew (the backup option has, again, absolutely nothing to do with database growth) you cannot device a plan how to reduce the size. As things stand right now, w/o actual root cause analysis, the verdict is that the database has the right size and should not be touched. We cannot recommend any action w/o actually knowing 1) if the database is actually larger than needed, 2) what part of the database is actually too large (reserved space consumed by ghosts, unallocated space, log VLFs etc) and 3) what is the action that causes growth.

Of course, you can always shoot first ask questions later (like any good self respecting DBA from Monument Valley would...) and attempt to shrink the database w/o further analysis, but I hope you're better than that.