• ok, lets see if I can cut through some of the issues here.

    a (.LDF) log file, will always grow, when you backup the log, you are removing all the transactions that have been flushed to disk. but it will not affect the log file physical size. it just increases the free space within the log file. the truncate option for the backup log chops off the free space at the end of the transaction log.

    To physically resize the log file, if it is too big, is to do a backup log, then use dbcc shrinkfile, and shrink the log file, this can cause fragmentation which you can sort later on, by rebuilding the indexes.

    Lets base this on an database called adventureworks

    To shrink the log

    USE [AdventureWorks]

    GO

    DBCC SHRINKFILE (N'AdventureWorks_Log' , 0, TRUNCATEONLY)

    GO

    to do a differential backup

    BACKUP DATABASE [AdventureWorks] TO DISK = N'c:\AdventureWorks.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    to do a log backup

    BACKUP LOG [AdventureWorks] TO DISK = N'c:\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    These are the default options.

    To fix your current situation, I would suggest doing a log backup, followed by a dbcc shrinkfile. that should bring the size of the log file down.

    You need to change the recovery model to full, otherwise you cannot do log backups. depending on the number of transaction per day, you could schedule your backup log every hour perhaps.

    Another thing to remember, in your maintenance plan you can also schedule log backups and shrinking files if needed.

    If you need clarification on any of this, let me know.

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]