> ----------
> From: Charles Caroline
> Sent: 04 October 2002 11:17
> To: 'Fang, Elaine'
> Subject: RE: trans log is still big after db backup
>
> Elaine, hope this helps:
>
> Truncating/backing up the log does not physically reduce the size of a log
> file, it simply marks virtual log files as inactive. Actually deleting
> portions of the log file is controlled by the DBCC SHRINKDATABASE and DBCC
> SHRINKFILE statements
>
> EITHER SET THE AUTOSHRINK OPTION (SEE BELOW) OR MANUALLY SHRINK (USE DBCC
> SHRINKFILE, SEE LATER IN DOC)
> Sections from BOL below:
> Autoshrink db option
> When true, the database files are candidates for periodic shrinking. Both
> data file and log files can be shrunk automatically by SQL Server. When
> false, the database files are not automatically shrunk during periodic
> checks for unused space. By default, this option is set to true for all
> databases when using SQL Server Desktop Edition, and false for all other
> editions, regardless of operating system.
> The only way to free space in the log files so that they can be shrunk is
> to back up the transaction log, or set trunc. log on chkpt to true. The
> log files shrink when the log is backed up or truncated. Therefore,
> setting autoshrink to true will cause the log to shrink only if the log is
> backed up or truncated.
> The autoshrink option causes files to be shrunk when more than 25 percent
> of the file contains unused space. The file is shrunk to a size where 25
> percent of the file is unused space, or to the size of the file when it
> was created, whichever is greater.
>
> DBCC SHRINKDATABASE (T-SQL)
> Shrinks the size of the data files in the specified database
>
>
> DBCC SHRINKFILE (T-SQL)
> Shrinks the size of the specified data file or log file for the related
> database.
>
> Unlike data files, the shrinking of log files is not immediate. Each log
> file is marked with the target size of the shrink operation. Each
> subsequent log backup or log truncation attempts to shrink the file and
> bring its size as close to the target size as possible.
>
> Regards, Caroline
>
>
>
>
> ----------
> From: Fang, Elaine[SMTP:Elaine.[Email Address Removed] Sent: 03 October 2002 14:02
> To: LazyDBA.com Discussion
> Subject: trans log is still big after db backup
>
> Dear list,
>
> Here is the question from one of my coworker who works in a different
> site:
>
> "The database still wasn't marked backed up this morning. I went ahead and
> backed it up to a disk device, C:\MSSQL7\BACKUP\TestlabBackup. I then
> truncated the transaction log, and then did another backup. This was all
> done from the Enterprise Manager interface. Now, the database does show it
> was backed up. The transaction log file size is still 81 MB, and for a 18M
> data file, that just doesn't sound right."
>
> According to him, this is not a very heavily traffic server, which is kind
> of weird for the trans log size.
>
> Any suggestions?
>
> Thanks,
> Elaine
>
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional commands, e-mail: mssqldba-[Email Address Removed]
>


____________________________________________________________________
"This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. This communication may contain material protected by
attorney-client privilege. If you are not the intended recipient or
the person responsible for delivering the email to the intended
recipient, be advised that you have received this email in error and
that any use, dissemination, forwarding, printing, or copying of this
email is strictly prohibited. If you have received this email in error
please notify the IT Call Centre by telephone on 44 (0) 1953 608000."

[Email Address Removed]MS Sql Server LazyDBA home page