November 5, 2013 at 4:47 pm
I have our SQL Server 2012 database doing a full backup every day and the backup file size is of course growing very large (about 80GB now) while the database itself is only 1.5GB. I could use a little direction or help on how to backup this database the best way. Basically I just want to be able to restore back about 30 days if needed, but I still think I should be doing a full backup each day. I noticed some options such as "Append to the existing backup set" or "Overwrite all existing backup sets". Can somebody help me understand that? Also, there is a setting for backup compression...
Any help would be appreciated.
Thanks!
November 5, 2013 at 6:45 pm
You have RECOVERY MODE in FULL and you are not doing a transaction log backup. Without doing some form of transaction log backup the tlog will grow indefinitely.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 5, 2013 at 7:40 pm
TheSQLGuru,
Thank you for this info. Can you help guide me through the steps to reduce the size based on your recommendations? Yes, my recovery mode is set to full, so should I set it to bulk-logged or simple? Also, with the backing up of the transaction log, can you explain a bit more with that? Does backing it up automatically reduce the size, or are you hinting at shrinking it's size? Sorry for the ignorance here, but any help or steps would be so much appreciated. Also, one other thing is that my backup is set to Full, should it be differential?
Thank you!
November 5, 2013 at 8:02 pm
The first question is which recovery model makes sense for your environment. Basically the question comes down to how much data can you lose? If you can lose 24 hours of data then a single full backup a day makes sense and your recovery model should be Simple. If you need to be able to restore to a single point in time you need to have your recovery model set to Full and take Tlog backups throughout the day (otherwise you log will grow forever like Kevin mentions).
Once you deiced which recovery model then you can tackle the Tlog size issue. If you set it to simple the amount of used space will be controlled by checkpoints (internal stuff that you don't have to worry about). If you set it to Full then Tlog backups will take care of the amount of used space. If the current size of your Tlog is too big then you will need to shrink the file (after doing the steps mentioned above).
The next issue you have (probably) is VLFs. Since your log has been growing uncontrollably you probably have way too many VLFs.
Here are some links to help:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply