Post reply

Transaction Log Question

  • February 15, 2013 at 10:05 am

    #289466

    Does anyone know why I would get the following error when the size of the transaction log for one of our databases is less than the max size? If that weren't the case, it would make sense to me and I would just shrink the file.

    The transaction log for database 'ourdatabase' is full.

    size_in_mbmax_size_in_mb

    466.81250002097152.0000000

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • February 15, 2013 at 10:17 am

    #1588416

    You could be out of disk space.

  • February 15, 2013 at 10:18 am

    #1588417

    Why do you want to shrink (make smaller) a log that is complaining it's full (not large enough)? Seems rather counter-productive to try and reduce the size of something that's too small.

    As for why, a transaction filled the log, grow maybe ran out of disk space or took too long and failed, transaction threw the log full error, rolled back and the log space was marked reusable. Hence the log was full when the error happened.

    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

  • February 15, 2013 at 10:21 am

    #1588420

    Hi Mike,

    What are the autogrowth settings on the log file?

    I think you can get that if the log file is full & can't grow quickly enough for the query that needs extra space.

    Cheers

    Gaz

  • February 15, 2013 at 10:33 am

    #1588424

    What Gail said.

    What is important for you to understand is why the log was filling up (unusually large transaction, unusually many transactions, index rebuild).

    Ideally you want to get to a point where you have your transaction log sized in such a way that it does not need to grow.

    Repeated incidences of transaction log growth, can fragment the log. Also each time the log grows, you get a stall in the given database for the n milliseconds it took to grow the log.

    So the question is not how can I make my systems stop squawking at me, but how can I size the log so that is stays in steady state and does not grow.

    You may want to set up monitoring jobs that try to back up the log if it is full beyond a a certain percentage, and email you the result of those attempts.

Viewing 5 posts - 1 through 4 (of 4 total)

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