• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • Last Modified:

Full Recovery Model - SQl server 2012

I have setup few of my databases to Full Recovery.
Also I setup a Maintenance Plan to take the transactional backup every 10 mins.


I need to confirm that will transaction logs cleared out after the Maintenance  plans runs and takes a backup of it?
0
yadavdep
Asked:
yadavdep
  • 3
  • 2
1 Solution
 
ZberteocCommented:
If you refere by clear as delete then only if you choose so from the maintenance plan. There is an option that will delete the files older than certain days/hours. You want to make sure that you don't delete transactions that were created AFTER last differential backup, if you have a differential or, if not, than AFTER the last FULL backup.

Ideally you should have a backup plan like this:

1. Weekly FULL backup
2. Daily DIFF backup
3. LOG backup every 10 min

In this case you will keep the LOG backups done in last 24 hr, so you wll choose the option to delete LOG files older than 24hr and same for the DIFF backups.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not sure what's your question about but a transactional log backup will truncate the transaction log so hopefully will have free space to store more transaction logs.
You should create a backup plan that includes Full backups (Weekly) and differential backups (daily) so you don't need to restore dozens or hundreds of transaction log backups in the case you'll need it.
0
 
ZberteocCommented:
If you refer by clear as in truncate then yes, after the LOG backup the transaction log files will be truncated, which means that the file size will still stay the same but it will be refill with new transaction in a cyclic manner.  NEVER shrink the transaction log files except in special cases where they grew too much due to some unexpected events.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
yadavdepAuthor Commented:
My main focus was on the entries in the  transaction logs only.

I wanted to know what happens to them after I do a transactional backup.

Are these entries removed from logs or not?
0
 
ZberteocCommented:
The virtual log files inside the transaction log are free to be used again after backup. Basically they are marked as empty so they can be written over but there is no need to actually be deleted. This is the long answer.

The short answer is that the data from log file is "removed" after the backup. so that the log file can be reused. Size will stay the same as before the backup.
0
 
yadavdepAuthor Commented:
Thanks Zberteoc for explaining it.

Thanks to others as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now