Link to home
Start Free TrialLog in
Avatar of AXISHK
AXISHK

asked on

SQL 2008 Backup

My database is configured with Full recovery mode and use tools inside MS SQL to backup the database.

Should I need to configure anything to truncate / remove the log after the backup ? Will these logs be removed automatically after the backup ?

Thx
SOLUTION
Avatar of strivoli
strivoli
Flag of Italy image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To clarify a bit, the backup releases the committed transaction data in the transaction log, the space the transaction log consumed will not be freed up.

Using regularly scheduled transaction log backup to maintain/manage the size of the transaction log.

The above suggestion eliminates the need to effectively reclaim space consumed by the transaction log as that is extremely counter productive as resources are consumed to free up space, that is then consumed requiring the SQL server to pause while the log available space is allocated.

Backup db
Between db backup setup transaction log backup at an interval suitable for your needs, every 12, 8, 6, 4, 2, 1, hours.......

The more frequent the transaction log, the lower the range of the log size. Certain transactions could require the log to be a specific size as the minimum log size.
Avatar of AXISHK
AXISHK

ASKER

To clarify my understanding,

Daily backup database will automatically allow those committed space in Log be usable. However, the physical will not change.

If I need to physically reduce the log size, I need to backup the log separately, correct ?

Thx
Yes, you would need to setup a separate job that backs up the transaction log. The frequency at which the transaction log will help you manage the size of the log, you can run this job more frequently, but it is not advisable to reduce the log size on a daily or other basis as noted before you make it inefficient and waste resources dealing with releasing the space that then the utilization will require that it grow.
If do not need the restore in time, change the recovery model to simple at which the log will revert to minimal size...
Avatar of AXISHK

ASKER

Can I say that under normal usage of the database, I only need to backup the database and no need to take care of the log backup, unless the it's size grow dramatically. Correct ?

Thx again.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial