Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002

    Question Unanswered: managing the transaction log


    I'm working with an SQL database that someone else has set up and this is a learning experience for me.

    I understand what the transaction log is and a little about it.

    What i would like to do is shrink it because it is full. If i use the wizard to truncate or shrink data it never seems to work. I have created a second log file but the server doesn't seem to use it. Increasing the log size does nothing also. DARN!

    What is the best way to dump the old data?

    thanks in advance?


  2. #2
    Join Date
    Oct 2002


    Q1 [i would like to do is shrink it because it is full]?

    A1 Note: one cannot shrink a 'Full' log beyond an active VLF; moreover, one must either dump / back up the contents of a transaction log to a transaction log backup *.trn file (or truncate it) before DBCC ShrinkFile can shrink the file to any smaller size.
    Frequently dumping / backing up your (production database) transaction logs to transaction log backup *.trn files will provide the means of point in time recoverability; and also keep the overall DB log size managable. (Typically, production user DBs should be using the Full backup recovery model.)

    General production guidelines include:
    i The use of DBCC ShrinkFile, (and / or enable autoshrink if appropriate).
    ii Identify any long running transactions that may be filling up your DB Log rewrite them to be efficient.
    iii Dump the DB transaction log to transaction log backup *.trn files as appropriate for the production environmen.t

    DBCC ShrinkFile advantages:
    * it is safe
    * it may be safely used even if your DB has multiple log files (add several additional log files to your DB, then rigorously test your method)
    * ordinary users may work in the DB while its files are being shrunk

    Use MyDB
    DBCC ShrinkFile ([MyDB_Log], 1, TruncateOnly)

  3. #3
    Join Date
    Feb 2002
    This was address a couple of weeks ago - check out the link:


Posting Permissions

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