Post reply

Backup transaction log file

  • January 26, 2009 at 8:00 am

    #125947

    Hi all,

    I got a message from our system monitoring saying I have to backup the transaction log file since it was full. However, I have got MSSQL job that has been running for a long time and that has been doing the backup tasks. Anyway, what does it mean that the transaction log file is full? I have sufficient hard disc space and I allowed the log file unlimited growth. Can anyone explain to me how such message should be treated?

    Thanks for your hint

    Niyala

  • January 26, 2009 at 8:25 am

    #932620

    I'd say the first thing to do is to double-check the settings on your log file, and make sure it's actually allowed unlimited growth and that it's actually on a drive that room to expand. Just worth double-checking.

    The other thing I'd look at it is are the log backups actually happening. Not database backups, log backups. Different thing entirely.

    Let us know what you find on those two things, and we might be able to help further if those don't handle it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 26, 2009 at 9:38 am

    #932666

    Has your job been doing full backups, or transaction log backups? In full recovery, you need both.

    Please read through this - Managing Transaction Logs[/url]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2009 at 1:33 am

    #933044

    Hi,

    I double checked my settings of the database as well as my disk space.

    My findings are:

    I have unrestricted growth of the logfile.

    The backup job is running, even after that particular event.

    The full-backup job is going perfectly well

    I have sufficient space on the back up disk

    And yet I do not understand why I got this message of 'log file is full'.

    Could there be any other reason for this?

    Thanks

    Niyala

  • January 27, 2009 at 3:56 am

    #933081

    Niyala (1/27/2009)

    The backup job is running, even after that particular event.

    The full-backup job is going perfectly well

    Do you have transaction log backups?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2009 at 3:59 am

    #933083

    Yes, indeed I have transaction log backup

    Thanks

    Niyala

  • January 27, 2009 at 4:09 am

    #933088

    How often? Is it succeeding?

    It's possible that the error came about when the log had to grow and the grow took too long. What's the auto-grow settings on the log file and what's its size?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2009 at 5:06 am

    #933104

    Many thanks.

    I allowed 10% auto-grow.

    Regards

    Niyala

  • January 27, 2009 at 5:17 am

    #933107

    How often are you doing log backups and are they succeeding?

    How big's the log file?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 27, 2009 at 5:52 am

    #933115

    Hi,

    I am doing every 60 minutes and so far all is working well.

    Regards

    Niyala

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply