Size of log file in SQL 7

[Mamzy]

Hi Everybody

First of all i would like to thank everyone for there time and efforts in this web page
I am new to the feild of DBA and i have some uncleared points that i would like any one to make them clear for me
Why the transactions log file size is not decreasing after the truncation of log?
is there any thing i have to do or is it normal way?

[Mike John]

When you 'truncate the log' it makes space available for re-use in the current physical log file, it does not actually decrease the current size of the file.

Everytime you backup the log - via backup log to a named file, sections of the log (known as Virtual Log Files - VLFs) are made available for re-use provided they do not contain data needed to rollback a currently incomplete transaction. Similarly if you do a backup log with truncate only - it makes the space available but does not copy the data out.

If you are running with 'truncate log on chkpt' or doing 'backup log with truncate only' at any time then you will not be able to reapply changes from the log after restoring from a backup ie the best you can do is back to when the last full or differential backup was taken. This may limit your career opportunities (at least with current employer) in the event of a disc failure on either the disc with the log on it, or the database file(s)!

Thus always be sure you understand the implications of backup log with truncate only.

If you wantto physically make the file smaller then dbcc shrinkfile can do it, in conjunction with backup log to mark the space as free. Loads more details in BOL, or Inside SQL Server 7 (or 2000) by Kalen Delaney.

HTH

Mike John

KnowledgePool ltd.
------------
Mamzy at 12/13/00 2:17:46 AM

Hi Everybody

First of all i would like to thank everyone for there time and efforts in this web page
I am new to the feild of DBA and i have some uncleared points that i would like any one to make them clear for me
Why the transactions log file size is not decreasing after the truncation of log?
is there any thing i have to do or is it normal way?