9

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:

SQL Server 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.

  • 3
    Can you explain what you mean by "truncate"? Do you mean you expect a log backup to shrink the log file? For what purpose? So it can grow again? – Aaron Bertrand Jun 6 '16 at 17:08
  • 3
    Also, I suggest you read this question and its answers for background before going any further: dba.stackexchange.com/q/29829/1186 – Aaron Bertrand Jun 6 '16 at 17:11
  • 3
    If you only need daily recovery, then stick to simple mode. (Why would you change to simple, then back to full? What do you think that accomplishes?) But if daytime is all reads your log shouldn't be changing during the day anyway. In any case, no, backing up the log will never shrink the log file. – Aaron Bertrand Jun 6 '16 at 17:23
  • 3
    If you go 6 months in full recovery mode without ever taking a log backup, yes, your log files will grow. However if, as you say, your only have read activity during the day, using full recovery mode is a waste, just make it simple. Then the log file will generally not grow at all (since in simple recovery mode the space for all but active transactions can be re-used). DBAs that know what they're doing generally use full recovery mode (so they can restore to a point in time), size their log files appropriately, and perform log backups frequently enough so that the log files don't grow. – Aaron Bertrand Jun 6 '16 at 17:49
  • 3
    Since you say you don't need point-in-time recovery, I'm not sure why you even consider the full recovery model an option., – Aaron Bertrand Jun 6 '16 at 17:51
7

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.

  • Awesome. So I've changed the recovery model to 'Simple' for all my databases, and ran Ola's script. Seems all I need to do now is to actually schedule the jobs created? – Mathieu Guindon Jun 6 '16 at 20:02
  • Yes please. Also, dont forget to mark as answer/upvote if the answer is the solution or helpful - this way it wont flag as unanswered. – Kin Shah Jun 6 '16 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.