Solved

Full Recovery Model - SQl server 2012

Posted on 2015-02-06
6
56 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 26

Accepted Solution

by:
Zberteoc earned 500 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 47

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 26

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 26

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

785 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