Post reply

Table Archiving Questions

  • January 19, 2012 at 4:03 pm

    #251576

    Is it possible to move a table partition from one database to another? Also, Is it possible to move a filegroup from one database to another?

    I'm interested in finding a new solution for archiving data. Currently, I am running insert statements followed by record deletions to move data.

    Any help is much appreciated.

    Thanks

  • January 19, 2012 at 4:52 pm

    #1436242

    I don't believe you can just issue a "move" command to relocate the table.

    However you can create a table in the new database and do a direct copy.

  • January 20, 2012 at 3:02 am

    #1436336

    rename table and create new table with similar name 🙂

    or create job for moving date

  • January 20, 2012 at 3:42 am

    #1436352

    sdruid71 (1/19/2012)

    Is it possible to move a table partition from one database to another?

    No. Different database, different files. You cannot "move" the pages from one to another, you will have to copy the data and drop the partition.

    You can move partitions between schema identical tables in the same database as its just about a metadata change.

    sdruid71 (1/19/2012)

    Also, Is it possible to move a filegroup from one database to another?

    No.

    IMO, your best option is to switch out the partition to another table and use another schedule to copy this to another database. This will leave contention on the original (in use) object to the partition switch only.

  • January 20, 2012 at 10:31 am

    #1436629

    One way to archive data in a single step is to use the OUTPUT clause of the DELETE statement. The procedure takes @MaxRows as a parameter, stating the maximum number of rows to archive.

    SET @StepCount = 1 -- The number archived in each "batch" (set to 1 to enter the loop).

    SET @RowCount = 0 -- The total number archived for this run.

    --<logic>For each batch of rows to archive:</logic>

    WHILE @StepCount > 0

    BEGIN -- Main Loop

    --<logic> - Determine how many rows to include in the batch.</logic>

    SET @BatchCount = CASE WHEN @MaxRows - @RowCount < 1000 THEN @MaxRows - @RowCount ELSE 1000 END

    --<logic> - Delete or archive the items.</logic>

    DELETE TOP(@BatchCount)

    FROM LiveData.dbo.WebLog

    OUTPUT Deleted.*

    INTO Archive.dbo.WebLog

    WHERE CreateDate < GETDATE() - 90

    SET @StepCount = @@ROWCOUNT

    SET @RowCount = @RowCount + @StepCount

    --<logic>Limit to the preset number of records.</logic>

    IF @RowCount >= @MaxRows

    SET @StepCount = 0

    END -- Main Loop

    Because it is a single statement, the OUTPUT into the Archive database and the DELETE from the main database occur in a single transaction. I added the TOP portion so that I don't get a locking issue with the process that adds the log records. It takes a little longer running in batches, but it keeps the transactions small so the archive process stays out of the way of the live transaction process, and the duration and memory of each batch stays small.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply