Home Forums SQL Server 7,2000 Backups Cannot shrink log file 2 (LOG FILE) because all logical log files are in use. RE: Cannot shrink log file 2 (LOG FILE) because all logical log files are in use.

  • As long as you remember it is best to avoid shrinks as per Gail's admonitions and Kimberly Trip's recent article on transaction log fragmentation (sqlservercentral.com/blogs/dba_tipster/archive/2009/12/17/transaction-log-fragmentation.aspx)

    [/url]

    According to MS, you should be able to shrink the log after a transaction log backup:

    "This article describes how to use the DBCC SHRINKFILE statement to shrink the transaction log file manually under the full recovery model in a SQL Server 2005 database".

    but it also goes on to say:

    "When you try to shrink a transaction log file that has little free space in SQL Server 2005, you may have to perform an additional log backup operation. The additional log backup operation truncates the transaction log file to a smaller size."

    Caution: the following is all theoretic !

    Since you had been manually re-running log backups, this might be your problem. Try doing the same thing in code.

    Do your job in a stored proc invoked from the Maint Plan or SQL Agent.

    In the stored proc, have something like that:

    BACKUP LOG databasename TO devicename

    label_for_Goto:

    BEGIN TRY

    DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

    END TRY

    BEGIN CATCH

    BACKUP LOG databasename TO devicename

    GOTO label_for_Goto --careful you don't spend the night re-running backups . . .

    --this script is offered as a sample and has never been used in practice ....

    END CATCH

    Also check Gail and Kimberly's info about what happens with REINDEX ... seems a gotcha will get you if you run it in the wrong order.