UNDO tables hold a history of changes (if the Changelog feature is enabled). For every change made to PANTHEON's database, a record is put into UNDO tables, along with who made the change and when.

UNDO tables are growing very fast and thus take up much space, potentially decreasing server database server performance and increasing backup size.

Moving UNDO tables to another location may:

• improve performance,
• decrease server load,
• decrease backup file size.

Moving UNDO Tables to a Secondary Server

Moving UNDO tables to a secondary server reduces the load of your primary server and keeping the data from UNDO tables archived. A recommended set-up is as follows:

All UNDO records over a certain age are moved from the primary to the secondary server. The moved data is not available online, but can be restored as needed.

First, create a database on the secondary server.

In the example below, the live database on the primary server is named PRI_SERVER.PANTHEON and the archive on the secondary server SEC_SERVER.ARHIV_UNDO. The primary and secondary server are linked servers.

On the primary server, create an SQL Server job that moves old records (older than 3 months in the example) to the secondary server. The job has two steps:

Update Structure

The structure of PANTHEON's database is often upgraded, that is why the structure of the archive database must be synchronized with the live database before moving data.

Move Data

The following script moves records from the most commonly used tables to the archive database. You can add or remove tables as necessary.

insert into SEC_SERVER.ARHIV_UNDO.dbo.BUDGET_UNDO
select * from PRI_SERVER.PANTHEON.dbo.BUDGET_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.BUDGET_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.BUDGETPOZ_UNDO
select * from PRI_SERVER.PANTHEON.dbo.BUDGETPOZ_UNDO 
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.BUDGETPOZ_UNDO 
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.CHSTATPROM_UNDO
select * from PRI_SERVER.PANTHEON.dbo.CHSTATPROM_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.CHSTATPROM_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.CONTACTS_UNDO
select * from PRI_SERVER.PANTHEON.dbo.CONTACTS_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.CONTACTS_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.CONTADDRESS_UNDO
select * from PRI_SERVER.PANTHEON.dbo.CONTADDRESS_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.CONTADDRESS_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.PROMET_UNDO
select * from PRI_SERVER.PANTHEON.dbo.PROMET_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.PROMET_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.PROMETPOZ_UNDO
select * from PRI_SERVER.PANTHEON.dbo.PROMETPOZ_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.PROMETPOZ_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.SERIALNOP_UNDO
select * from PRI_SERVER.PANTHEON.dbo.SERIALNOP_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.SERIALNOP_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.SUBJEKT_UNDO
select * from PRI_SERVER.PANTHEON.dbo.SUBJEKT_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.SUBJEKT_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.TEME_UNDO
select * from PRI_SERVER.PANTHEON.dbo.TEME_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.TEME_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

insert into SEC_SERVER.ARHIV_UNDO.dbo.TEMEPOZ_UNDO
select * from PRI_SERVER.PANTHEON.dbo.TEMEPOZ_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())
delete from PRI_SERVER.PANTHEON.dbo.TEMEPOZ_UNDO
where _dl_LastCh < DateAdd(mm,-3,GetDate())

Moving UNDO Tables within the Same Server

Moving UNDO tables from one database to another on the same server resolves the issue only partially. The live database's size is decreased, but the server load is not significantly reduced, because it still spends some resources (cache, memory) on the archive database.