How to trancate transaction log?

I use the backup wizard to run a transaction log backup with option to remov
e
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.Are you using the full or bulk-logged recovery mode? I guess you are if the
backup completes successfully. You probably have an uncommitted transaction
that's preventing the log being truncated. Look at the output from DBCC
OPENTRAN to determine the age of the oldest uncommitted transaction (on SQL
2000)
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
> I use the backup wizard to run a transaction log backup with option to
remove
> inactive transaction logs. After completing the backup, the size of
> transaction log file does not shrink. Why? Thanks.|||Hi
The physical size of the log file will not change when you do a backup -
that will only truncate the log. To reduce the filesize, you'll have to
shrink the file.
You can try to look up DBCC SHRINKFILE in BOL - and also check out
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
Regards
Steen
joeau wrote:
> I use the backup wizard to run a transaction log backup with option
> to remove inactive transaction logs. After completing the backup, the
> size of transaction log file does not shrink. Why? Thanks.|||Removing unused transactions means incorporating those committed
transactions in the full backup as well.
Truncation does not imply file shrinkage, just the removal, actually just a
mark on the virtual logs, that those transactions have completed and backed
up.
Although the transaction log file may not have been shrunk like you'd like,
because all of the old transactions have committed and backed up, the
transaction log does not have any reason to grow either because it can now
use those freed up virtaul logs.
Sincerely,
Anthony Thomas
"joeau" <joeau@.discussions.microsoft.com> wrote in message
news:01EAAADA-8F83-4CF4-A0A3-AE776092F7EB@.microsoft.com...
I use the backup wizard to run a transaction log backup with option to
remove
inactive transaction logs. After completing the backup, the size of
transaction log file does not shrink. Why? Thanks.