SQL 2000 - Maintenance Plan Transaction Log

Question

SQL 2000 - Maintenance Plan Transaction Log

Roger Abram

Hall of Fame

Points: 3683
More actions
May 9, 2008 at 6:39 am

#121539

If a database is included in a maintenance plan and it backs up the database and the transaction log, does it actually truncate the log?
I know that if I backup a log manually there is an option to remove inactive entries. I don't see that option in the maintenance plan so was curious if the maintenance plan is doing it or not.
Thanks.
Rog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

If you run a transaction log backup, the inactive portion is truncated and reused. Maintenance plan or not.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

@RadM SSC Rookie Points: 42 More actions · Answer 2

I have the same question :

SQL 2000

I want to run a backup of transaction log with the option

" remove inactive entries from transaction logs " using T SQL ,

but am not able to find the right switch / command for same.

Also when I run a full backup then free space in the log file does not change.

1. After Transaction Log Backup with " Remove inactive entries "

from enterprise manager shows reduction in Log Space Used (%) .

Using the DBCC SQLPERF ( LOGSPACE ) to check this.

2. After full backup scheduled as part of maintenance job

there is no reduction in Log Space Used (%) .

Please Help !!

ps. One Orange Chip Points: 29255 More actions · Answer 3

@RadM (11/23/2010)
I have the same question :

You could have opened a new thread for your query since this thread is too old.

SQL 2000
I want to run a backup of transaction log with the option
" remove inactive entries from transaction logs " using T SQL ,
but am not able to find the right switch / command for same.

backup log yourdb to disk='complete path here'

This will clear out your inactive log entries from the transaction log file, making those freed-up space re-usable. There is no separate switch for this.

Also when I run a full backup then free space in the log file does not change.

Full backup has no effect on transaction log. It does not releases any space from the tran log.

2. After full backup scheduled as part of maintenance job
there is no reduction in Log Space Used (%) .

This is the correct behaviour. As stated earlier, full backup does not have any effect on the transaction log free space.

Pradeep Singh

@RadM SSC Rookie Points: 42 More actions · Answer 4

@RadM

SSC Rookie

Points: 42

November 24, 2010 at 4:28 am

#1254987

Thanks a lot Pradeep !