asked on

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?

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

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.

Zberteoc

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.

yadavdep

ASKER

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?

Zberteoc

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.

yadavdep

ASKER

Thanks Zberteoc for explaining it.

Thanks to others as well.