Results 1 to 10 of 10

Thread: Transaction Log Size

  1. #1
    Join Date
    Sep 2007
    Posts
    14

    Transaction Log Size

    Hello dear friends,
    In my database i am not able to take the backup of transaction log.
    Even if i took the back up and then shrunk the file it doesnt make any change to my transaction log size.
    Still the size is same.
    So in every two weeks i am restarting my server.
    Once it is restarted then it is ok for another 2 weeks.
    After 2 weeks my transaction log size will be more than the size of my datafile.
    Can you suggest your openion.
    No replication or log shipping exist.

    Thank you

  2. #2
    Join Date
    Jun 2007
    Posts
    41
    use backup log with TRUNCATE_ONLY option

  3. #3
    Join Date
    Sep 2002
    Posts
    5,938
    Backup log will not shrink log file size either. Did you get any error when you try to backup log? What's recovery mode the db is? Post details if you need help.

  4. #4
    Join Date
    Sep 2007
    Posts
    14
    Hello shamshe and rmiao,
    Thank you for the response.
    I tried both commands like
    backup log dbname with no_log
    backup log dbname with_truncate only.
    the result is 'done sucessfully'
    then i shrunk the transaction log file.
    That was also success.
    No error generating.
    But after all these when i look on the database the transaction
    log size is same.
    No change to the size.
    give your ideas please.

    Thank you
    Ichayan

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    You have to check how much free space in the file, don't sgrink it if server has enough free disk space.

  6. #6
    Join Date
    Sep 2007
    Posts
    14
    Hi rmiao,
    now my log file size is about 40 gig.
    every day we have 4 complete backup.
    As the log size is increasing dramatically the backup size also
    become big.
    how can i solve.
    Thank you

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    You have to do log backup to free log space if the db is not in simple recovery mode.

  8. #8
    Join Date
    Sep 2007
    Posts
    14
    yes, i have done that too.
    even then no change to the log size.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    That's correct, backup never change file size. How often do you backup log?

  10. #10
    Join Date
    May 2003
    Location
    India
    Posts
    15
    Why dont u try this.

    Backup the log file and then shrink the logfile using the DBCC shrinkfile command with the argument as the target size..
    Its similar to HWM in oracle.

    Pavan.

Posting Permissions

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