Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    London, UK

    Unanswered: Unable to truncate the transaction log


    Within SQL Ent Manager - I am unable to truncate the tran log using the shrink file option window (although I can shrink the database file) - nor can can I truncate the log using command line sql in a query analzer window (dump tran < > with truncate only)....

    Anyone out there offer any suggestions??


  2. #2
    Join Date
    Dec 2004
    California, USA
    There are circumstances (e.g., Active portion of the log is at the end of the file) where the normal steps that shrink the log file won't work. Books Online covers the wheres and whys and describes how to get around this.
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Dec 2004
    Madison, WI

    Truncating the Tran Log (or shrinking it)

    I had a similar problem but I couldn't shrink the Tran Log no matter what I tried. What I did was this and it seemed to work:

    1. Changed the backup schema from Simple to Bulk-Logged (or vice versa).
    2. Changed it back to Simple (or vice versa).
    3. Ran the Shrink Database from the GUI.

    It then shrunk and truncated the Tran Log.

    Hope this helps in some way. Don't know why it works but it did.

  4. #4
    Join Date
    Jan 2005
    You could try running this in query manager - it's what I use and it works fine (actually have it set up on sql agent to run periodically)

    backup log db_name with truncate_only

    dbcc shrinkdatabase (db_name,10)

  5. #5
    Join Date
    Jul 2003
    San Antonio, TX
    Unless the size of the log is a 10th multiple of the data, or if it's grown to a critical limit (application architecture needs to be revised to set the proper recovery mode), - why do you try to force shrink it? It does not affect the size of the backup device for trx as well as full backups. You're actually hidering the performance by shrinking the size, because apparently the size needed is higher than the resullting size after shrinking.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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