SQL Server Performance Forum – Threads Archive

Archiving Data Older than 30 Days

I need to be able to archive data older than 30 days from specific tables, from one server to another, where the table names end with a unique 3 charaters. I hava a date column to reference and ORDER BY.
I need to perform this task with 25 tables (some with over 10,000,000 rows). I’m not really understanding what going on with the section in RED. It seems to me that to simply perform the archive of data older that X number of days/months, I could simply repeat the process in BLUE for all 25 tables.
CREATE PROC dbo.ArchiveData
(
@CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP) END
ELSE
BEGIN
IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR (‘Cannot delete orders from last three months’, 16, 1)
RETURN -1
END
END BEGIN TRAN INSERT INTO Archive.db :confused:

rders
SELECT *
FROM db :confused:

rders
WHERE OrderDate < @CutOffDate IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while copying data to Archive.db :confused:

rders’, 16, 1)
RETURN -1END INSERT INTO Archive.db :confused:

rderDetails
SELECT *
FROM db :confused:

rderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM db :confused:

rders
WHERE OrderDate < @CutOffDate
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while copying data to Archive.db :confused:

rderDetails’, 16, 1)
RETURN -1
END DELETE db :confused:

rderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM db :confused:

rders
WHERE OrderDate < @CutOffDate
) IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while deleting data from db :confused:

rderDetails’, 16, 1)
RETURN -1
END DELETE db :confused:

rders
WHERE OrderDate < @CutOffDate IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while deleting data from db :confused:

rders’, 16, 1)
RETURN -1
END IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END END

What’s wrong with it? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

You need to perform the archive process in 3 stages in order to validate the data load & deletion. 1) Export required rows from primary server and copy to archive server and , by using BCP or DTS or code.
2) Load the exported rows to the archive server and check the rowcount.
3) Delete the rows exported by validating with a date field or ident number. THe above process can be controlled using an ArchiveControl on the primary database, where you will never miss the rows to load/delete. This can be scheduled by using SQLAgent jobs. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Nothing is wrong with the query marked in RED,It is just Bcoz of huge rows. Instead of Using IN Cluase U try to join the OrderDetails And Orders Table. INSERT INTO Archive.db :confused:

rderDetails
SELECT X.*
FROM db :confused:

rderDetails X , Y db :confused:

rders
WHERE X.OrderID=Y.OrderID And
OrderDate < @CutOffDate
Sandy (DB Developer)

Also better to reduce indexes on that table in order to speedup the load performance, for such big loads why not use BULK INSERT for performance gain. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>