February 24, 2009 at 2:22 pm
Hello All,
I am trying to archive data from OLTP database into Archive database. Below is the procedure which I am using currently. In this procedure, I have added few checks like making sure number of records inserted into archive database in equal to the number records deleted from OLTP database. Please see the procedure......In where clause, I am using datetime field .Like for example, whatever data is older 60 days, archive it.
But now the requirement is to add few more checks as the size of the database is huge:
1) If I want to run this archive process only during off peak time (ie.) only 300 minutes (i.e.) 5hrs and then I want to check # of inserts = # of deletes.................Here the challenge is , delete is happening after data gets inserted into Raw table ( please see code below). So , if I stopped the process of inserting data into RAW table afer X minutes , but when I am calculating # of deletes to comparte with the row count of RAW table, It will display incorrect results................
2) I am quite sure that many of the DBA's here have done this thing in the past (i.e) Data archiving. If someone can share his code, it would be awesome.
Thanks. for your help.
CREATE PROCEDURE [dbo].[Archive_version2]
AS
SET NOCOUNT ON
DECLARE @Csvr VARCHAR(255),
@Msvr VARCHAR(255),
@vsProc VARCHAR(255),
@Ivd INT,
@Icd INT,
@dtWorkDate DATETIME
DECLARE @Insertcount INT
DECLARE @Deletecount INT
SELECT @Csvr = RTRIM(Value)
FROM Conf
WHERE Identifier = 'DM'
SELECT @Msvr = RTRIM(Value)
FROM Conf
WHERE Identifier = 'BN'
SELECT @dtWorkDate = DATEADD(DAY, -60, GETDATE())
-- Archive data older than 60 days
SET @vsProc = @Csvr + '.Database_IC.dbo.archive_Insert_Raw'
-- Code for 'archive_Insert_Raw', mentioned after this procedure
INSERT INTO D_Raw
(
D_SessionID,
VID,
DSN,
Started_Dt,
Ended_Dt,
Server_Name,
IP_Address,
Di_List,
APSP_Resent_Cnt,
Created_Dt
)
EXECUTE @vsProc @dtWorkDate
-- Checking inserts in Raw table = Inserts from OLTP table
SELECT @insertcount = COUNT(*)
FROM D_Raw
SELECT @deletecount = COUNT(*)
FROM Database_IC.dbo.Digital_S
WHERE Started_Dt < @dtWorkDate
IF @insertcount <> @deletecount
BEGIN
TRUNCATE TABLE D_Raw
PRINT 'insert & deletes are not equivalent'
SELECT @insertcount
SELECT @deletecount
RAISERROR ( 'Cannot perform archiving as inserts are not equal to delete',
16, 1 )
RETURN -1
END
ELSE
BEGIN
PRINT ' Successful Archiving '
SELECT @insertcount AS Number_of_Inserts
SELECT @deletecount AS Number_of_deletes
-- Delete data we just added
SET @vsProc = @Csvr
+ '.Database_IC.dbo.archive_delete_OLTP'
EXECUTE @vsProc @dtWorkDate
-- Code for 'archive_delete_OLTP', mentioned after this procedure
/* Copy data from D_Raw to Digital_Session */
BEGIN TRANSACTION
INSERT INTO D_S_archive
(
D_SessionID,
VID,
DSN,
Started_Dt,
Ended_Dt,
Server_Name,
IP_Address,
Di_List,
APSP_Resent_Cnt,
Created_Dt
)
SELECT D_SessionID,
VID,
DSN,
Started_Dt,
Ended_Dt,
Server_Name,
IP_Address,
Di_List,
APSP_Resent_Cnt,
Created_Dt
FROM D_Raw
TRUNCATE TABLE D_Raw
COMMIT TRANSACTION
END
---------------------------------------------------------------
create procedure [dbo].[archive_Insert_Raw]
( @iVD int, @dtWorkDate datetime )
AS
SELECT Digital_SessionID,
VID,
DSN,
Started_Dt,
Ended_Dt,
Server_Name,
IP_Address,
Digital_AppID_List,
APSP_Resent_Cnt,
Created_Dt
FROM Digital_OLTPTable
WHERE VID = @iVD
AND Started_Dt < @dtWorkDate
--------------------------------------------------------------
create procedure [dbo].[archive_delete_OLTP]
( @iVDint, @dtWorkDate datetime )
AS
DELETE
FROM Digital_Session
WHERE VID = @iVD
AND Started_Dt < @dtWorkDate
-------------------------------------------------------------------
February 24, 2009 at 2:31 pm
There's a feature in SQL 2005 called "output", that can return the values from an insert. You could use that to detect which exact rows were successfully inserted into the archive tables, and then use that to know which rows to delete from the OLTP database. Look it up in Books Online, it's very useful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 5:45 am
Described archive and purge strategy is not sustainable on a large production environment.
I would suggest to re-architect involved tables so you can rely on partition switching; this is going to be faster, cleaner, cheaper and specially you are going to see no performance degradation over time -as opposed as insert/delete strategy that over time would have a negative, cumulative effect on performance. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply