Today, one of our backups failed due to the transaction log being full. I expanded the transaction log and then the backup completed normally. For a short term resolution, I increased the maximum allowed size on our transaction log files.
We are doing a database backup only as follows:
backup database xxxxx to xxxx_data with init
I did find a note stating that the transaction log will eventually become full if doing a transaction log backup only. We do not generate a lot of transactions so I had not seen a need to backup the log during the day. The full database backup is done daily.
My question is, in order to permanently resolve this error do I have to:
- take a transaction log backup as well. Should I take it take right after the full database backup or at some later point.
- do I have to schedule/run the shrink database command.
I would respectfully disagree with the note that you found about transaction log eventually getting full if doing trx log dumps only, simply because of the nature of the log architecture and what a backup does to it (either full or log dump only.) The ONLY reason the log eventually is getting full is due to uncommitted transactions, which CANNOT be cured by ANY backup!
As per frequency and sequence of trx log dumps, of course you need to do it before the full backup, providing your db is set for Full or Bulk-Logged Recovery mode.
I suspect the database is already in Simple Recovery mode. And if there is a lot of data being updated/inserted/deleted in large bulks, - this will not take care of the problem, because when checkpoint occurs, not all of the transactions are committed, which results in growth of the log file (see my previous post.)