Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By DrorHarari

Thread: many ATTREP_NET_CHANGES tables appearing in target

  1. #1
    frandsb is offline Junior Member
    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    many ATTREP_NET_CHANGES tables appearing in target

    Hello-

    For last couple of weeks, we have been observing the creation of tables with names starting with "attrep_net_changes000" in the target database. I cannot correllate their creation times with any event in the logs. Most are empty tables. We are seeing multiple per day being created, and they are cluttering the database. Would appreciate coming to know why they are created, and if there is a way to keep them from persisting.

    Thanks!
    Brent

  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    These are tables that are used for bulk loading change data to the target database. Replicate uses a few of those tables and they should not accumulate. Please contact support for additional help.
    frandsb likes this.
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    frandsb is offline Junior Member
    Join Date
    Jul 2012
    Posts
    8
    Rep Power
    0

    Will do, thanks

    I will contact support for that. In the meantime, I have switched tasks from batch optimized apply to transactional apply to keep those tables from coming...
    Cheers!
    Brent

  4. #4
    Tony Mou is offline APAC Support Manager
    Join Date
    Sep 2006
    Location
    China
    Posts
    35
    Rep Power
    0

    A script to clean those net changes table

    These are used by turbo stream CDC to apply changes to target.

    They should be deleted when task is stopped (each task is deleting its own table, not all tables). If many of these tables left in target DBs, you can stop all tasks and then use following script to drop them:

    Certainly, better let your DBA review the scripts first before running it

    use [<TargetDB>]

    DECLARE curItems CURSOR
    FOR SELECT name FROM sys.sysobjects WHERE TYPE='U' and name like 'attrep_change%'
    FOR READ ONLY
    OPEN curItems
    DECLARE @n NVARCHAR(100),@m NVARCHAR(100)
    FETCH FROM curItems INTO @n
    WHILE @@FETCH_STATUS=0
    BEGIN
    set @m=@n
    exec('Drop Table ' + @m)
    FETCH NEXT FROM curItems INTO
    @n
    END
    CLOSE curItems
    DEALLOCATE curItems
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  5. #5
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    165
    Rep Power
    13

    version? Redirect Metadata schema owner?

    Thanks Tony.

    I have also had a report on this for a PDW target, but the problem has not been seen since we started using Replicate 3.1.0-30 and later.
    What version is the customer using? Can they upgrade (if the think they can not, then please explain why not?)
    Although the version may have played a role, we think the DBA's altered roles and privs during our test, making the issue disappear.

    Should the problem persist, be sure to consider to move (the clutter of) those metadata tables away from the main tables in the task settings:

    Name:  Capture_metadata_1.JPG
Views: 334
Size:  27.7 KB

    For SQLserver targets, one may also want to direct the internal table to their own file group using the advance settings for the target DB under Manage Databases

    Name:  Capture_internal_tables.JPG
Views: 350
Size:  32.3 KB

    Good luck!
    Hein

  6. #6
    Tony Mou is offline APAC Support Manager
    Join Date
    Sep 2006
    Location
    China
    Posts
    35
    Rep Power
    0
    Thanks Hein

    Tony
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  7. #7
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    165
    Rep Power
    13

    Replicate V4 creates a single unique attrep_changes table per task, not per startup.

    Before Replicate V4, replicate would create a unique attrep_changes with the timestamp of the task start every time a task was (re)started.
    If the task did not end normally those could indeed accumulate.

    This will no longer be an issue with Replicate V4 because the attrep_changes table will always get the same single unique number, thus a new activation can and will cleanup any temp table left behind.

    Hein

Posting Permissions

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