Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Question Unanswered: Avoid increasing the log file

    Hello All,

    I have faced a network problem during some days, what forced one of our replications to be stopped.
    The Publisher database is a high volume database.
    After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted.

    My concern is the way it is working, there will be no enough space for the log or for its backup files.

    So, I have created a TSQL job within the following commands:

    BACKUP LOG database_name
    WITH TRUNCATE_ONLY
    DBCC SHRINKDATABASE (database_name,TRUNCATEONLY)

    It's running every 20 minutes, however the transaction log remains increasing.

    I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process.

    Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication?

    Thanks a lot!
    Regards,
    Felicia Schimidt
    felicia.schimidt@br.flextronics.com

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: Avoid increasing the log file

    Don't really know anything about internals on MS replication...

    On Sybase ASE, the replication generates a secondary truncation point. In your situation, it's possible to disable this 2nd point. Without disabling, it's not possible to truncate the part between the 1st and the 2nd truncation point.

    Check on MS-SQL whether it's possible to execute a dbcc gettrunc()... or something "like"

  3. #3
    Join Date
    Jul 2003
    Posts
    16

    Unhappy Re: Avoid increasing the log file

    Hi,

    Thanks for your help, but I could not find anything like gettrunc()...

    Please, does anybody know anything about the issue below?
    I am really in trouble!

    Thanks again!
    Felicia


    Originally posted by fadace
    Don't really know anything about internals on MS replication...

    On Sybase ASE, the replication generates a secondary truncation point. In your situation, it's possible to disable this 2nd point. Without disabling, it's not possible to truncate the part between the 1st and the 2nd truncation point.

    Check on MS-SQL whether it's possible to execute a dbcc gettrunc()... or something "like"

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    If you're truncating the log with TRUNCATEONLY you are effectively destroying it, so you might as well ALTER DATABASE the database to SIMPLE RECOVERY mode, and you won't get a transaction log at all.

    In essence, your real problem happens because a significant portion of the log is 'active' - in other words holding an open transaction, at the point the you are issuing the truncate command. Do you have a large long running transaction hanging fire?

    Generally, you'll be better off either putting very large storage behind a real transaction log and backing off the log properly, or doing away with it altogether if your recovery policy will permit. Growing and shrinking a transaction log repetitively will lead to considerable disk fragmentation over time and cause performance problems particularly as you obviously have a very large load.

    also

    "For each database that will be published in transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database may require more space than the log of an identical, unpublished database. This is because the log records may not be purged until they have been moved to the distribution database.

    "If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been passed into the distribution database (unless replication is turned off completely for that database). It is recommended that you set the transaction log file to autogrow so that the log can accommodate these circumstances."

    http://msdn.microsoft.com/library/de...lplan_1l4e.asp
    Last edited by HanafiH; 01-26-04 at 11:21.

  5. #5
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    Agree with HanafiH. A single replicated transaction could consist of tens of thousands of commands. Those operations will cause subscriber log to grow until the last command is commited. In your case, putting the subsriber database in simple recovery mode, and/or getting enough disk space to allow largest potential log growth, is the best option.

    You may also want to turn off "auto shrink" to avoid performance issue.

  6. #6
    Join Date
    Jul 2003
    Posts
    16

    Red face

    Hi,

    Thanks for you help.

    I have setup the database to simple recovery mode and turnning subscriber agent on.
    Unfortunnatelly the Transaction log grew again quickly and no disk space was availble.

    Is there any other way to force the database to not use the transaction log?

    Thanks a lot,
    Felicia

    Originally posted by rding
    Agree with HanafiH. A single replicated transaction could consist of tens of thousands of commands. Those operations will cause subscriber log to grow until the last command is commited. In your case, putting the subsriber database in simple recovery mode, and/or getting enough disk space to allow largest potential log growth, is the best option.

    You may also want to turn off "auto shrink" to avoid performance issue.

  7. #7
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    some non-logged operations don't seem to fit in your scenario. You probably need to reset up the replication.

Posting Permissions

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