Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Full Recovery Model - SQl server 2012

Posted on 2015-02-06
6
Medium Priority
?
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 52

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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public 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 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 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