Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: shrinking log file

    When I try to shrink the log file for my databases I am getting following message.

    Cannot shrink log file 2 (cm_archive_mp_Log) because all logical log files are in use.
    what should I do to successfully shrink the log file.
    Thanks.

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    the log file is divided into multiple logical log files. If any of these logical files have uncommitted or pending transactions in them, they cannot be shrunk. One of the ways to shrink the log file is :

    (1) Stop & restart SQL server service - this will force checkpiont on database which may then clean log of dead transactions.

    (2) Write a script to do a lot of inserts & deletes into a temporary table in the database - this will cause the log file to fill up so you can then truncate the inactive portion of the log.

    (3) Put database into SIMPLE recovery mode ( if SQL 2000 ) or check Truncate on Checkpoint under options if SQL 7. This will force truncate of log file so it wont grow very much at all. Then flick back into FULL backup mode.

    Cheers,

    SG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •