I'm a beginner in DB administration so bear with me. I have a small DB 500MB with a very large transaction log (19GB). I'd like to keep this in "Full Recovery" mode, so please do not suggest "Simple" recovery mode.
I have been researching about how to reduce the size of the Transaction logs and I'm trying to implement the suggestions but the log size does not change.
First of all, here's what I do:I have a daily backup task that backups all the databases within a maintenance plan. It's a "Full" backup type and it seems to work well. I see one file backed up every day similar to the size of the DB itself.
Now that I have a full backup, I've proceeded with doing a manual "transaction log" type of backup which with the option "truncate the transaction log".
The backup completes in a few seconds, creates a file with a size of a few megabytes but the size of the transaction logs stay the same.
What am I doing wrong?
Best Answer
Your transaction log backup is truncating the logs in the sense that it's making room in the existing log file for more transactions. If you want to shrink the log file, you need to choose the "shrink file" option in SSMS. Right click the database to find that option.
If the file size you shrink it to isn't large enough, based on how many transactions your database has and how often you back up the logs, it'll grow again. You might need to run log backups more frequently to prevent this.
It sounds like you're not running transaction log backups at all (except the one time you ran it manually). If so, not only is this why your transaction log is growing, but you're not getting the benefits of full recovery, anyway. Please schedule regular transaction log backups. (Hourly would be a good start. )