cancel
Showing results for 
Search instead for 
Did you mean: 

Full SQL Back-up and log truncate

BSV
Not applicable
Hello,

The SQL-log on an remote server is about 60 GB.

- Symantec back-up exec 12 SP3
- SQL server 2005 (remote server)

Case:

The logfile is growing and growing.
The Back-up method: Full - back up entire database or filegroup. methode will not truncate the log.
  • The SQL recovery mode must set on full. (our customer wil have a full back-up of the data)
  • Recovery mode of SQL server is set to Full. (our customer wil back-up all transactions)
What is the best practice for this case?

---
Information about the SQL database:
Microsoft SQL Server Management Studio Express      9.00.2047.00
Microsoft Data Access Components (MDAC)      2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML      2.6 3.0 4.0 6.0
Microsoft Internet Explorer      8.0.6001.18702
Microsoft .NET Framework      2.0.50727.3082
Operating System      5.2.3790

4 REPLIES 4

pkh
Moderator
Moderator
   VIP    Certified

Doing a Full backup on a SQL database will not clear the transaction log and it will grow.  What I do is: Run a job specifying Log - Backup transaction log JUST after I have done a Full backup.  This log backup job will backup the transaction log and clear it.
 

Martin_Forster1
Level 5
HI

Recoverymode only says, that the sql engine does not clean merged transactions on its own.

So yes, Full Recovery is great.
a Full Backup with BE seems to leave the transaction log untouched by default.
However the size of the ldb will not shrink, its just empty, after a Log Backup.

But you need to perform incremental Backups, which are basically just backups of the transaction.
THIS Job will cleanout your transactions.

i personally have such jobs somethimes scheduled in 5 min steps, which is very fast.
But normally not necessary, nor would i suggest in normal usage case.

a starting point http://seer.entsupport.symantec.com/docs/310362.htm
and a best practise is also available
http://support.veritas.com/docs/311707
 


Regards

Martin Forster


Terry_Sposato
Level 3
I am doing a Full Backup in a separate Policy Once a week followed by differential backups during the day.
In another policy I am doing a Log Backup although the actual ldf file isn't shrinking in size?
Is there something here I am overlooking?

The database recovery model is set to Full, the log backup works and runs fine.
Is there something different I need to be doing here to shrink the file as well?

pkh
Moderator
Moderator
   VIP    Certified

This is an old discussion.  Do start a new discussion for your problem.  You may refer to this discussion for reference.