LDF won't autoshrink

21 Sep 2006 2:44 AM

Charles

I'm running SQL 2000 SP4 on a Win2003 server...

I have a DB that is about 30GB in size. I notice every month or so I need to
do a backup of the DB and transaction logs through Enterprise Manager. After
that I run a "shrinkdb" to flush the LDF file. My question is; shouldn't
this happen automatically? I'm running nightly backups via BackupExec.

Thanks,

21 Sep 2006 3:01 AM

Hari Prasad

Hi,

Looks like your database is having either FULL or BULK_Logged recovery
model. In this case you need to do a trasnaction log backup
to clean the log file; otherwise the log file keeps growing. You could use
MAINTENANCE PLAN to schedule a transaction log backup.

For the time to reduce the log file size do this:-

1. Issue the command - BACKUP LOG <dbname> with Truncate_only
2. Use DBCC SHRINK FILE to shrink the log file. See books online for usage
3. Do a full database backup to rebuild the backup chain
4. schedule the transaction log backup

This will ensure that your log file will not grow drastically

Thanks
Hari
SQL Server MVP

Show quote

"Charles" <char***@aol.com> wrote in message
news:uGz73eS3GHA.1256@TK2MSFTNGP02.phx.gbl...
> I'm running SQL 2000 SP4 on a Win2003 server...
>
>
>
> I have a DB that is about 30GB in size. I notice every month or so I need
> to do a backup of the DB and transaction logs through Enterprise Manager.
> After that I run a "shrinkdb" to flush the LDF file. My question is;
> shouldn't this happen automatically? I'm running nightly backups via
> BackupExec.
>
>
>
> Thanks,
>
>

21 Sep 2006 2:01 PM

Charles

Thank you for your reply. I will try this and repost.

Show quote

"Hari Prasad" <hari_prasa***@hotmail.com> wrote in message
news:OBQOVsS3GHA.1796@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> Looks like your database is having either FULL or BULK_Logged recovery
> model. In this case you need to do a trasnaction log backup
> to clean the log file; otherwise the log file keeps growing. You could use
> MAINTENANCE PLAN to schedule a transaction log backup.
>
> For the time to reduce the log file size do this:-
>
> 1. Issue the command - BACKUP LOG <dbname> with Truncate_only
> 2. Use DBCC SHRINK FILE to shrink the log file. See books online for usage
> 3. Do a full database backup to rebuild the backup chain
> 4. schedule the transaction log backup
>
> This will ensure that your log file will not grow drastically
>
> Thanks
> Hari
> SQL Server MVP
>
>
> "Charles" <char***@aol.com> wrote in message
> news:uGz73eS3GHA.1256@TK2MSFTNGP02.phx.gbl...
>> I'm running SQL 2000 SP4 on a Win2003 server...
>>
>>
>>
>> I have a DB that is about 30GB in size. I notice every month or so I need
>> to do a backup of the DB and transaction logs through Enterprise Manager.
>> After that I run a "shrinkdb" to flush the LDF file. My question is;
>> shouldn't this happen automatically? I'm running nightly backups via
>> BackupExec.
>>
>>
>>
>> Thanks,
>>
>>
>
>

21 Sep 2006 10:45 PM

Erland Sommarskog

Charles (char***@aol.com) writes:

> I have a DB that is about 30GB in size. I notice every month or so I
> need to do a backup of the DB and transaction logs through Enterprise
> Manager. After that I run a "shrinkdb" to flush the LDF file. My
> question is; shouldn't this happen automatically? I'm running nightly
> backups via BackupExec.

No, you don't want the log file to shrink automatically. That would
only cost you performance when it grows again. Shrinking files - data
or log - is only of interest when there has been execeptional things
happening.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx