Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    20

    Unanswered: Transaction log size problem

    Hi

    I am importing my Access tables in Sql server 2000. When I set the primary key for one of my tables which has 35 corer records it throws message as trans log is full please backup the log files.

    Is there any way to disable log writing. If not how I can solve this problem. I have used "Truncate log on checkpoint" and "Select into bulk copy" options but didn't find any solutions.

    Can some one please help me on this.

    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    Milwaukee, WI
    Posts
    7

    Re: Transaction log size problem

    Is the recovery model set to full or bulk-logged? The truncate log on checkpoint is only active when the backup model is set to simple.

  3. #3
    Join Date
    Nov 2002
    Posts
    20

    Re: Transaction log size problem

    Originally posted by schustet
    Is the recovery model set to full or bulk-logged? The truncate log on checkpoint is only active when the backup model is set to simple.
    Yes we have set the recovery model set to full and backup model is set to simple.

    But still we are facing this problem.

  4. #4
    Join Date
    Mar 2003
    Location
    Milwaukee, WI
    Posts
    7
    There is no way to turn off logging.
    You have control over the following transaction log attributes:
    You can control the transaction log incremental growth rate.
    You can control the max transaction log size.
    You can move the transaction log to a larger drive or split it across multiple drives.
    When set to simple recovery model, you can control the size of transactions and commit them as often as possible to allow the db to release transaction records.
    When not using simple recovery model, you can back up the transaction log more frequently to free up transaction records.
    You can use bulk-logged recovery model to reduce the transaction log entry content during bulk transfers.

  5. #5
    Join Date
    Nov 2002
    Posts
    20
    Originally posted by schustet
    There is no way to turn off logging.
    You have control over the following transaction log attributes:
    You can control the transaction log incremental growth rate.
    You can control the max transaction log size.
    You can move the transaction log to a larger drive or split it across multiple drives.
    When set to simple recovery model, you can control the size of transactions and commit them as often as possible to allow the db to release transaction records.
    When not using simple recovery model, you can back up the transaction log more frequently to free up transaction records.
    You can use bulk-logged recovery model to reduce the transaction log entry content during bulk transfers.
    Thanks for the reply.

    At present I have created the table in sql server with primary key and imported data from table which doesn't have primary key using query in ranges.

    I am facing one more problem: Is it necessary to set the primary key? If primary key is not set then it doesn't allow to add, update and delete any of the records in the Access.

    Can you help.

  6. #6
    Join Date
    Feb 2002
    Posts
    5

    Force Log Shrink

    Due to the way the Transaction Log works (you can think of it as a table with each row being a transaction) the entire log may not be shrinkable. This is because the unused parts of the t'log need to be near the beginning of the file. If there is a 'dirty' part of the transaction log near the end of the file with a lot of 'clean' parts before it, the clean parts can not be cleared and the log can not be shrunk.

    There are ways around this though, if you keep adding data and deleteing data eventually the data will be committed and it dirty sections will loop around to the beginning of the file. There is a SP that can fix this for you, which I will attach.

    Execute this script (creates a SP) in the database that you need the log truncated, then execute the SP from that DB.
    Attached Files Attached Files

Posting Permissions

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