Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Shrink the database. –  ta.speot.is Aug 20 '12 at 11:30
2  
i keep reading that shrinking the db is 'evil'... what does it actually do –  raklos Aug 20 '12 at 11:32
    
Shrinks the database... You might have heard it referred to as evil because it can cause a lot of index fragmentation. –  ta.speot.is Aug 20 '12 at 11:48
    
See Remarks here –  ta.speot.is Aug 20 '12 at 11:50
    
I would wrorry that your host is not correctly backing up the database log. I'm suprised to see a log that large. IN a transactional database the logs shouldbe backed up much more frequently than the database - we do our every 15 minutes but a t the very least, I would set them up for hourly. If you do not back up the transaction log, it will grow until you run out of spece on the server. Best to nip this in the bus right now. BTW, you know that 600mg is a teeny tiny database, right? –  HLGEM Aug 20 '12 at 14:54
add comment

migrated from stackoverflow.com Aug 20 '12 at 15:08

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.