Moving data from Transaction database to Archive/Reporting database. After the records has been moved, delete the records to avoid duplicates.

Note:

  • If your database is involved in AlwaysOn or in Full Recovery mode, make sure you backup the database and log file to clear out the log files on the database before preceding.
  • After you have finish with the record move, you should clear out the log files on the database to avoid running out of log space.

Duration

ActionRecordsDurationLog Growth
INSERT20 million records27:15 minutes
DELETE20 million records1:33 minute
INSERT 50 million records1:00:24 hour112GB after 70 million record inserts
DELETE 50 million records 7:00 minutes55GB after 70 million record deletes

Example

SET IDENTITY_INSERT [Server01].[MyDb01].[dbo].[tbMyTable] ON

INSERT INTO [Server01].[MyDb01]..[dbo].[tbMyTable]  
(
	[iColId]
	,[vcCol]
)
SELECT      
	[iColId]
	,[vcCol] 
FROM 
	[Server02].[MyDb02].[dbo].[tbMyTableARCH] WHERE iColId > 1000000

SET IDENTITY_INSERT [Server01].[MyDb01]..[dbo].[tbMyTable] OFF
Last modified: May 24, 2021

Author

Comments

Write a Reply or Comment