Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Left over tables after failed replicate/refresh

sf_ReplicateAll and sf_RefeshAll have both failed a couple times due to a Sf outage. There are a bunch of _Deleted and _Delta tables left in my database. Is there a built-in way to clean these up or do I need to build my own script?
1 person has
this question
+1
Reply
  • They can be deleted but I do not have script to do so.

    We leave them for diagnostic purposes when the cause of the failure is not known.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Thanks for the quick response Bill! Here is the script I'm using now. I attached no ownership/license, feel free to share with anyone.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    Create PROCEDURE [dbo].[SF_CleanupWorkTables]
    AS
    BEGIN
    /* Call me when you have excess Delta and/or Deleted tables left over for DBAmp. Often happens
    when Sf API has an outage. This proc works until Y3K. Check criteria below for table name
    conflicts with your existing tables before running.
    */
    DECLARE @name VARCHAR(255)
    DECLARE @sql NVARCHAR(300)
    DECLARE tableList CURSOR FOR
    select Name from sysobjects where xtype='U' and (name like '%[_]Deleted2%' or name like '%[_]Delta2%')

    OPEN tableList
    FETCH NEXT FROM tableList INTO @name

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @sql = 'drop table [' + @name + ']';
    print @sql
    execute sp_executesql @sql
    FETCH NEXT FROM tableList INTO @name
    END

    CLOSE tableList
    DEALLOCATE tableList

    END
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited sad, anxious, confused, frustrated