Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Unanswered: No translogdumps = logspace overflow?

    I have a server which has all its databases dumped every night. Twice every hour the transactionlog is dumped to file. Due to yet unkown circumstances the diskspace became too low to make any transactionlogdumps somewhere last night. Later the whole server grinded to a halt due to no space in the logsegment.

    Does the logspace gradually fills when the transactionlog isn't dumped anymore?
    I'm not crazy, I'm an aeroplane!

  2. #2
    Join Date
    Mar 2007
    Posts
    72
    Hi Martin,
    The transaction log in a database is like a recording mechanism that keeps track of all changes that happens (insert, delete, update) in the database(s) Since your system is a production environment ( I guess) all transactions need to be recorded and backed up to disk, os any time the server crashes, or watever happens you can get your data back by loading the last dump and applying the transaction log dumps, sequentially, so if there is no space for the tran logs dumps, then the tran log keeps growing (dboption, trunc log on checkpoint is obviously turned off here, in your circumstance), when no more space is available for transactions to be logged the server just freeses until space is made available in the log.

    hope this helps

    suda

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Martijnvs
    I have a server which has all its databases dumped every night. Twice every hour the transactionlog is dumped to file. Due to yet unkown circumstances the diskspace became too low to make any transactionlogdumps somewhere last night. Later the whole server grinded to a halt due to no space in the logsegment.

    Does the logspace gradually fills when the transactionlog isn't dumped anymore?
    Yes, what you could do is do a dump tran with truncate_only on your last threshold action. So, if your dump trans fail for some reason, you have a last resort atleast. But the downside is subsequent transaction log dumps will fail until you do a full backup again.

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Thank you both. I managed to track down the culprit. There was an unclosed transaction in syslogshold, from almost 1 mnth ago . The process it belonged to was still active, but didn't do anything, that's why it didn't got my attention earlier. The logdumps kept growing and growing, until diskspace ran out, and the logdumps began failing.
    I killed the process, dumped the databases, and all returned back to normal. Freed almost 20 Gb of discspace .

    I will be looking in to some protective measures to prevend this from happening again. It's no fun being called in the middle of the night to fix such a problem (or rather, it's no fun for my coworker who was on call-duty :P).
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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