Improve company productivity with a Business Account.Sign Up

x
?
Solved

Full Recovery Model - SQl server 2012

Posted on 2015-02-06
6
Medium Priority
?
65 Views
Last Modified: 2015-02-22
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
Comment
Question by:yadavdep
  • 3
  • 2
6 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 40593826
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
 
LVL 54

Expert Comment

by:Vitor Montalvão
ID: 40593837
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40593861
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:yadavdep
ID: 40594231
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40594508
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
 

Author Comment

by:yadavdep
ID: 40594992
Thanks Zberteoc for explaining it.

Thanks to others as well.
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question