Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    52

    Question Unanswered: How to reduce the transaction log file

    Hi all,

    I know this topic has been discussed in the past, but I still don't quite get it. So please be patient with me

    database size created with automatically growth of 10% with unrestricted file growth.

    database size = 5 Gb used 4.5 GB (taskpad)
    transaction log=8GB used 54MB (taskpad) 7.5 GB free
    database run in FULL mode

    full backup nightly, transaction log backup every 30 min

    What should I do to free up the space that are not used in the transaction log.


    Thanks for your help.

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: How to reduce the transaction log file

    DBCC SHRINKFILE
    ( { file_name | file_id }
    { [ , target_size ]
    | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
    }
    )

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How big is your hard drive?

    If it's like 90GB...don't worry about it....

    I'd be more worried though about unrestricted growth....

    but you maint plan seems pretty good, and 7 gb for a tranny I would doubt would blow out....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    Doesn't your half hourly transaction log backup clear the space automatically?

  5. #5
    Join Date
    Feb 2004
    Posts
    52
    Originally posted by suresh_m_kumar
    Doesn't your half hourly transaction log backup clear the space automatically?

    No. that is why I don't understand

  6. #6
    Join Date
    Nov 2003
    Posts
    54
    The tranny backup every 30 min will truncate the log, but transaction log file (*ldf) is not automatically shrunk witht he truncation - see the post above with the syntax for DBCC shrink file - once you do this, the 30 min backups should keep the log to a more manageable size.

  7. #7
    Join Date
    Feb 2004
    Posts
    52

    Re: How to reduce the transaction log file

    Originally posted by joejcheng
    DBCC SHRINKFILE
    ( { file_name | file_id }
    { [ , target_size ]
    | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
    }
    )


    What option should I take : Truncateonly ??
    [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY }

  8. #8
    Join Date
    Nov 2003
    Posts
    54
    You will want to use truncateonly I belive, so the freed space will be allocated back to the OS. Read this for an explanation of the options, or use the BOL:

    http://msdn.microsoft.com/library/de..._dbcc_8b51.asp

Posting Permissions

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