Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2007
    Posts
    8

    Unanswered: SQL Server 2000 SP3 transaction log errors

    Hi!

    We have a Microsoft SQL Server 2000 SP3 running database for Microsoft
    Navision 3.7

    From time we encounter problems, especially when running heavy query
    procedures from Navision, with the transaction log. It's actually setup as
    folows:

    File properties:
    File growth By percent (10)
    Restrict file growth (MB) 10000

    OPTIONS:
    Recovery model: simple
    Settings: Autoupdate statistics, Auto create statistics, Autoshrink

    We get the following errors (once every 2-3 months so far):

    The log file for database 'ME_Prod' is full. Back up the transaction log for
    the database to free up some log space..


    in between numerous abovementioned messages I have the following:
    Configuration option 'show advanced options' changed from 1 to 1. Run the
    RECONFIGURE statement to install..

    Could not write a CHECKPOINT record in database ID 9 because the log is out
    of space.

    Automatic checkpointing is disabled in database 'ME_Prod' because the log is
    out of space. It will continue when the database owner successfully
    checkpoints the database. Free up some space or extend the database and then
    run the CHECKPOINT statement.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Remove Autoshrink option and increase the size of the log to 20GB. Navision is yet another Paul Alan's "gift" to MS, which is almost as "good" if not better than the ones before, so the term heavy means a lot of data to be modified to accomodate for the poor design of that "masterpiece". Of course you can't follow the message advice and backup the log, because the db is in Simple recovery mode. But for what it's worth, - do not shrink the log, neither manually nor with Autoshrink setting.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2007
    Posts
    8
    I've deselected AutoShrink option and I will set the transaction log file size to 20Gb. As for the file growth ,what would be the better approach. Keep it on 10% or maybe it's better to select In megabytes? If in megabytes, what is the best practice? How do I determine which value to set?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Specifically for NAVision, I'd recommend setting the log size relatively high. I'd start at about 50% of your data size, which would be 5000 Mb (which is also 5 Gb). I'd set the log file growth to somewhere between 64 and 128 Mb in order to prevent queries from timing out during log file growth.

    You probably want to examine the use of tempdb too. NAVision makes extensive use of tempdb, so it needs to be sizd appropriately too (which depends on what else is running on your SQL Server).

    -PatP

  5. #5
    Join Date
    Dec 2007
    Posts
    8
    Our DB size is about 110 Gb. Is it required to set 50% transaction log even if a recovery model is simple?
    And how do I set up an increment if it's preferred to use Mb instead of %?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Go ahead and set whatever works for your installation.

    Based on post #1 in this thread, your log file is limited to 10 Gb now. I'd set the initial size to 20 Gb and the limit to whatever suits you, but at least 50 Gb.

    File growth comes in two flavors, percentage and fixed. You are currently using percentage file growth, which means that each growth increment is computed based on the size of the file at the time that growth is needed, which implies that larger files grow by larger amounts and that no two growths are the same size. Fixed growth is based on the assumption that you set the original size appropriately (so that growths are rarely needed), and it makes all of the growths the same size.

    Percentage file growth is easy, and a good choice for a database that isn't managed by a DBA. These databases are usually managed by a system administrator, a power user, or all too often by no one at all.

    Fixed file growth performs much better, but it requires a bit of work to get it right. The DBA needs to understand (or measure) how the database grows, anticipate most of the growth, and may have to actively manage the file growth on occasion to support the database growth.

    -PatP

  7. #7
    Join Date
    Dec 2007
    Posts
    8
    I have one more question with this regard. If I set a 'Space allocated' to 20 Gb and will set 100 Mb growth for up to 50 Gb. When the log becomes 50 Gb and there's no more space to write, what actually happens? Does the log start rewriting itself? It will not shrink since I deselected the Autoshrink option right, so means, it will remain always 50 Gb? I don't really understand very much how it functions.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Once the log size reaches the limit, you will start getting the same error as you posted in your original message.

    As I mentioned in my first post, - Navision is not an app that plays "nice". I would discourage allowing auto growth, because you can't predict when the growth will actually happen, and when it does, - your performance will drastically degrade. In order to properly size the log device, you will need to get a better handle on how much data gets modified during those large data modification times that Navision occasionally has. Once you identify the modifiable amount of data, - then you can establish the fixed log size + additional buffer so that you don't have to readjust it more often than once every 3 months.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Dec 2007
    Posts
    8
    I was actually thinking that in simple mode when there's no more room for writing transaction log starts rewriting itself. Is that wrong?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, the log won't ever write over active log information.

    SIMPLE mode causes the database engine to reclaim log space that is not used by active transactions. A transaction is always active from the BEGIN TRANSACTION until either the COMMIT TRANSACTION or the ROLLBACK TRANSACTION. There are other factors like replication that can leave a log entry active for longer periods, but for now I'll assume that those don't apply in your case.

    When the log reaches the limit that you've imposed (the maximum log size), if log space can't be reclaimed then the database engine will no longer write to the log, which implies that it will no longer accept any changes to the database. That is what causes the message (from post #1) that started this thread.

    -PatP

  11. #11
    Join Date
    Dec 2007
    Posts
    8
    I see. And how can i get around this problem? How do I set up the log in such manner, that it won't trouble me with this issue? What is the best practice for that?
    We have been using the database with the log file limited to 10 Gb for quite a long time, years actually. But now we start having this issue.

Posting Permissions

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