[dba-SQLServer]Truncate Transaction Log

Thank you everyone for their responses but not sure if it is what I am 
looking for.

I am looking for a way of maintaining the virtual size of the transaction 
log, not the physical size of the transaction log.  After truncating the 
transaction log, the physical log file remains the same.  It is not until 
you shrink the transaction log that the physical log reduces in physical 
size.

If I understand this correctly, the transaction log consists of an active 
and inactive portion.  The active portion is used to restore the database 
since last backup.  The inactive portion is just old records that need to be 
deleted.  When you backup the transaction log, SQL Server truncates the 
inactive portion so that it can be reused.

Then how come the transaction log keeps physically growing in size even 
though full backups and transaction log backups are being applied daily?  
Because the transaction log is being backed up, the inactive portion is 
truncated and should be reused, thereby the transaction log physical size 
should remain the same.  You shouldn't need to shrink down the transaction 
log if you are applying transaction log routinely, right? because the 
inactive portion of the log is reused.

Thanks in advance,
Billy


>From: "Eric Barro" <ebarro at afsweb.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: RE: [dba-SQLServer]Truncate Transaction Log
>Date: Wed, 13 Aug 2003 14:15:05 -0700
>
>Billy,
>
>You need to shrink the Tlog. I would make sure that the Tlog doesn't grow 
>over a certain size or it will eat up all the disk space you have. 
>Depending on the volume of transactions you might want to back up the Tlog 
>on a regular schedule and then schedule a job to shrink it every night. You 
>will probably have to go through several shrink operations before you are 
>able to recover back all the unused space.
>
>---
>Eric Barro
>Senior Systems Analyst
>Advanced Field Services
>(208) 772-7060
>http://www.afsweb.com
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Billy
>Pang
>Sent: Wednesday, August 13, 2003 1:58 PM
>To: dba-SQLServer at databaseadvisors.com
>Subject: [dba-SQLServer]Truncate Transaction Log
>
>
>Hello:
>
>I have a database using FULL Recovery Model.  The Transaction Log is about
>10 times the size of the Data File.  I just performed a Full Database
>Backup.  How do I reuse the inactive portion of the Transaction Log?
>
>Thanks in advance,
>Billy
>
>_________________________________________________________________
>Add photos to your e-mail with MSN 8. Get 2 months FREE*.
>http://join.msn.com/?page=features/featuredemail
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.509 / Virus Database: 306 - Release Date: 8/12/2003
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.509 / Virus Database: 306 - Release Date: 8/12/2003
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.  
http://join.msn.com/?page=features/featuredemail