Best way to backup & truncate transaction logs on a schedule

Question

I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance.

So for a while I've been having my SSIS overnight process run a Execute SQL Task to perform the backups - basically running master.dbo.xp_create_subdir to ensure the destination folders exist, and then BACKUP DATABASE [DbName] TO DISK = 'G:\Backups\DbName\DbName.bak' WITH INIT.

Whenever that task failed, the rest of the process would abort and I'd get a notification, and come in the next morning to notice the drive for transaction logs was filled to capacity, and so I'd manually truncate them and move on... until the story repeated itself and the transaction logs outgrew the available disk space again.

The "manual truncate" script looks like this:

use Staging;
alter database Staging set recovery simple
alter database Staging set recovery full
dbcc shrinkfile ('Staging_log', 0, truncateonly);
go

So I'm growing tired of this, and I decided to try and do things properly instead, and follow the steps here and create an actual maintenance plan:

Thing is, I've never done this before, so I have a few questions:

• Will backing up the transaction logs like this automatically truncate them, or there's something else I need to do?
• Is it okay to run data and transaction log backups concurrently? If not, then what's a proper way of doing this?
• The backup files are being picked up overnight by another process that grabs all the files on the server and stores them elsewhere - would it be a good idea to expire the backup set after 2 days? Do I need to make them expire at all?
• Cleanup tasks respectively remove "old" .bak and .trn files under the subfolders of G:\Backups. Does that make sense?
• Would it be better to do this in SSIS, so I can fail my ETL if/when the backups fail? Or should my ETL process even care?

Sorry if this is too many questions for one post, if needed I'll edit and ask multiple questions instead - I think they're all tightly related though.

Answer 1

Only overnight SSIS is making writes, daytime is all reads - I only need daily recovery.

You should choose your recovery model based on your business needs :

• How much data business can loose and at the same time survive ?

Based on the above answer, you should carefully choose your database recovery model.

In simple terms (not discussing bulk logged recovery model),

• A full recovery model allows log backups that allows point-in-time recovery.
  • Log truncation can occur when you take transaction log backups i.e. Log file space will be reused after each log backup and wont bloat !
• A Simple recovery model only allows you to take FULL backups. Point-in-time recovery is not possible.
  • Log truncation can only occur when a checkpoint occurs (manually or automatic) i.e since you do regular full backups, you do not have to worry about transaction log as CHECKPOINT will take care of reusing the inactive portion of the log file.

Remember that log truncation is NOT physical reduction in size of transaction log file It means that the inactive portion of the transaction log file is marked as reusable.

Hence you should properly presize your transaction log file (and data files). Growing the log file will kick in autogrowth events (if your database is set to autogrow as a last resort). Check my answer - Autogrowth - Percentage Use?

---

I would highly suggest you to ditch maintenance plans and implement [a smart maintenance solution - that is easy, flexible and follows best practices] - 5. - Ola's backup solution (and Index maintenance solution as well).

---

lets address your questions :

Will backing up the transaction logs like this automatically truncate them, or there's something else I need to do?

Please do not append backups or set them to expire. They create a big mess. Use INIT and take separate log backups with datetime stamp. Easy to maintain. Use Ola's backup solution for that. The solution is flexible to delete old backups as well.

Is it okay to run data and transaction log backups concurrently? If not, then what's a proper way of doing this?

A full backup has no effect on a T-log backup. A full backup contains only enough transaction log necessary so that in the event of a restore, the database can be transactionally consistent to the time at which the data reading portion of the full backup completed. Check - how much transaction log a full backup includes ?

Also, a log backup during a full backup will not truncate the transaction log. A (couple of) log backup/s after the full backup finishes will truncate the log.

The backup files are being picked up overnight by another process that grabs all the files on the server and stores them elsewhere - would it be a good idea to expire the backup set after 2 days? Do I need to make them expire at all?

Cleanup tasks respectively remove "old" .bak and .trn files under the subfolders of G:\Backups. Does that make sense? Would it be better to do this in SSIS, so I can fail my ETL if/when the backups fail? Or should my ETL process even care?

For above both, use Ola's backup maintenance solution. It will take care of deleting old files.