Solved

Full Recovery Model - SQl server 2012

Posted on 2015-02-06
6
54 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 46

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now