July 31, 2015 at 10:11 pm
Several years ago I wrote code to archive Data.
I created a table that set the maximum number of records to archive and delete and loop until finished or a set a flag in a table to stop archiving. I was able to limit the number of records to commit.
I wish I had the code. It was a little complicated.
Does anyone have code to do this?
Any input would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 1, 2015 at 4:58 pm
Does anyone have a script to archive data?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2015 at 8:37 am
Welsh Corgi (8/1/2015)
Does anyone have a script to archive data?
Seems like a rather trivial task but as always it depends. How would you identify the data eligible for archiving? Is partitioning (table or view) and option?
😎
August 2, 2015 at 9:37 am
I would prefer not to go with partitioning.
About ten years ago I created at table that captured the start and end date. The number of records Committed etc.
I wrote code that had a While loop that had a Begin Transaction and it committed the number of records as specified in the table. It also looked on the active flag and if it was set to false it would not execute the WHILE Loop.
Edit: It would first archive the data to another database then delete the records from the bas table.
I would use a Datetime stamp to identify which records should be archived.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2015 at 9:45 am
seems fairly simple , i would personally use partitioning since it handles the whole process with far less stress on the DB but it would be just as easy to create an ssis package to do the same. If you could share the table structure I am sure I can drum something up in a few hours.
August 2, 2015 at 9:54 am
Welsh ...your ideas from a few years back...maybe jog your memory 🙂
http://www.sqlservercentral.com/Forums/FindPost1172289.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 2, 2015 at 9:59 am
Jayanth_Kurup (8/2/2015)
seems fairly simple , i would personally use partitioning since it handles the whole process with far less stress on the DB but it would be just as easy to create an ssis package to do the same. If you could share the table structure I am sure I can drum something up in a few hours.
I did not bring my laptop home but I can send you something first thing Monday. Thank you so much.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2015 at 10:02 am
J Livingston SQL (8/2/2015)
Welsh ...your ideas from a few years back...maybe jog your memory 🙂
Yeah really I'm shooting blanks when it comes to recall what I did.:blush:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2015 at 11:38 am
I'd be really careful when considering partitioning (either partitioned tables or views) just for purposes of archiving. There are a whole lot of caveats (for example, partitioned column is added to all unique indexes in partitioned tables making them non-unique unless it's the only column in the index) to partitioning to the point that I tell people "if you partitioned a table and thought it was easy, you probably did something wrong". 😉 Yeah... it's real nice to do nearly instant SPLITs and the like but there can be a lot of other baggage that goes along with it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2015 at 3:00 pm
I wish that I could get some example code to perform this.
I'm Swamped at the moment.
Thanks.:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2015 at 6:18 am
Jayanth_Kurup (8/2/2015)
seems fairly simple , i would personally use partitioning since it handles the whole process with far less stress on the DB but it would be just as easy to create an ssis package to do the same. If you could share the table structure I am sure I can drum something up in a few hours.
Here is the table structure. It is ugly. I did not create it.
Thank you for your help.
CREATE TABLE [dbo].[tblCall](
[Call_ID] [int] NOT NULL,
[Call_Date] [datetime] NULL,
[Call_Time] [datetime] NULL,
[Operator_ID] [smallint] NULL,
[Reviewer_ID] [char](30) NULL,
[Insurer_ID] [int] NULL,
[DivisionID] [int] NULL,
[Insurer_Name] [char](50) NULL,
[Insurer_Approval_Comments] [text] NULL,
[Insurer_Daily_Comments] [text] NULL,
[Adjuster] [char](30) NULL,
[CSR_ID] [char](30) NULL,
[CSR_Name] [char](30) NULL,
[CSR_Phone] [char](50) NULL,
[CSR_Ext] [char](10) NULL,
[CSR_Conf] [char](1) NULL,
[CSR_Conf_Date] [datetime] NULL,
[CSR_Conf_Time] [datetime] NULL,
[Insured_Full_NM] [char](50) NULL,
[Insured_FName] [char](14) NULL,
[Insured_LName] [char](16) NULL,
[Insured_Address] [char](30) NULL,
[Insured_City] [char](20) NULL,
[Insured_State] [char](2) NULL,
[Insured_Zip] [char](10) NULL,
[Insured_HPhone] [char](50) NULL,
[Insured_WPhone] [char](50) NULL,
[Insured_CellPhone] [char](50) NULL,
[Insured_Pager] [char](50) NULL,
[Insured_Email] [char](100) NULL,
[Insured_AltPhone] [char](50) NULL,
[Insured_Comments] [text] NULL,
[ThirdParty_Claimant] [char](50) NULL,
[ThirdParty_Phone] [char](50) NULL,
[ThirdParty_AccessName] [char](50) NULL,
[ThirdParty_AccessPhone] [char](50) NULL,
[Policy] [char](25) NULL,
[Claim] [char](25) NULL,
[Deductible_Amt] [money] NULL,
[Loss_Date] [datetime] NULL,
[Loss_Location] [char](30) NULL,
[Loss_Address] [char](30) NULL,
[Loss_City] [char](20) NULL,
[Loss_State] [char](2) NULL,
[Loss_Zip] [char](10) NULL,
[Loss_Descr] [text] NULL,
[Loss_Comments] [text] NULL,
[CONTR_ID] [int] NULL,
[Contractor_Name] [char](50) NULL,
[Contr_Rep] [char](30) NULL,
[Contr_Fax_Date] [datetime] NULL,
[Contr_Fax_Rec_Date] [datetime] NULL,
[Contr_Fax_Rec_Time] [datetime] NULL,
[Contr_Insured_Date] [datetime] NULL,
[Contr_Insured_Time] [datetime] NULL,
[Contr_Appt_Date] [datetime] NULL,
[Contr_Appt_Time] [datetime] NULL,
[Contr_Comments] [text] NULL,
[Contr_GetJob] [char](1) NULL,
[Contr_Cancellation] [char](1) NULL,
[Contr_Assigned_Date] [datetime] NULL,
[PRISM_Just_Dev] [char](1) NULL,
[PRISM_Frst_Est_Date] [datetime] NULL,
[PRISM_Frst_RevReq_Date] [datetime] NULL,
[PRISM_Received_Date] [datetime] NULL,
[PRISM_Closed_Date] [datetime] NULL,
[PRISM_Inspection_Date] [datetime] NULL,
[PRISM_FollowUp_Date] [datetime] NULL,
[PRISM_Comments] [text] NULL,
[PRISM_Frst_Review_Date] [datetime] NULL,
[PRISM_ClaimClosed_Date] [datetime] NULL,
[Owner_ID] [int] NULL,
[DateRec_ID] [int] NULL,
[Creator_ID] [int] NOT NULL,
[CALL_LST_REV_ID_NB] [int] NULL,
[CALL_LST_REV_SER_NB] [int] NULL,
[CALL_LOCK_ID_NB] [int] NULL,
[CALL_BIX_FILE_NM] [varchar](80) NULL,
[CALL_LST_SENT_DT] [datetime] NULL,
[CALL_RCV_EN_IN] [char](1) NULL,
[Est_System] [char](3) NULL,
[CALL_STAT_CD] [char](20) NULL,
[Contr_WorkAuthorization] [char](1) NULL,
[Contr_EMail] [varchar](80) NULL,
[PRISM_ClaimCancellation] [char](1) NULL,
[Loss_Location_Same] [char](1) NULL,
[Nexus_ID] [int] NULL,
[Loss_Cause] [int] NULL,
[CommitDate_Con] [datetime] NULL,
[CommitDate_Ins] [datetime] NULL,
[CommitDate_Est] [datetime] NULL,
[Insured_Company] [varchar](50) NULL,
[PRISM_Frst_RevRec_Date] [datetime] NULL,
[CommitDate_Rev] [datetime] NULL,
[Type_ID] [int] NULL,
[Has_Been_Cancelled] [tinyint] NULL,
[Claim_Cancelled_Reason] [int] NULL,
[Program_Option_ID] [int] NULL,
[ClientAdminResource] [int] NULL,
[CustSurveyTag] [tinyint] NULL,
[ReinspectTag] [tinyint] NULL,
[RandomNum] [real] NULL,
[BilledClient] [tinyint] NULL,
[BilledContractor] [tinyint] NULL,
[billedContrDate] [datetime] NULL,
[billedAssign] [tinyint] NULL,
[billedAssignDate] [datetime] NULL,
[USAAregionName] [varchar](200) NULL,
[singleTradeListID] [int] NULL,
[IsDirty] [tinyint] NULL,
[custSurveyPendDate] [datetime] NULL,
[callDateFull] [datetime] NULL,
[roofInspectionFlg] [bit] NULL,
[USAAprogramArea] [varchar](200) NULL,
[xactTransactionIDValue] [varchar](50) NULL,
[profileCode] [varchar](5) NULL,
[contrEmailSentFlg] [bit] NULL,
[Reinforcement_FLG] [bit] NULL,
[JobStartOverrideDate] [datetime] NULL,
[THDSentFlg] [bit] NULL,
[AddlEstNeeded] [bit] NULL,
[CheckPointPriceList] [varchar](25) NULL,
[PriceList] [varchar](25) NULL,
[ReferralFlg] [bit] NULL,
[MRPSupplementDate] [datetime] NULL,
[CATAssignment] [bit] NULL,
[RvwFlg] [bit] NULL,
[CMS_NextFollowUpDate] [datetime] NULL,
[TestFlg] [bit] NULL,
[CatCode] [varchar](10) NULL,
[PendingCancellationFlg] [bit] NULL,
[PendingNCFFlg] [bit] NULL,
[ReferralId] [varchar](25) NULL,
[CallCenterRepAssigned] [int] NULL,
[isBeingHeld] [bit] NULL,
[XADataSet] [varchar](100) NULL,
[CC_CATCode] [varchar](30) NULL,
[SymbilityClaimId] [uniqueidentifier] NULL,
[SymbilityClaimAssignmentId] [int] NULL,
[SymbilityIntermediateClaimAssignmentId] [int] NULL,
[Loss_BuildYear] [int] NULL,
[Target_Followup_Date] [datetime] NULL,
CONSTRAINT [PK_tblCall] PRIMARY KEY CLUSTERED
(
[Call_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]
) ON [FG1] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Operato__0CEFF2A1] DEFAULT (0) FOR [Operator_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Insurer__0DE416DA] DEFAULT (0) FOR [Insurer_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__Deducti__0ED83B13] DEFAULT (0) FOR [Deductible_Amt]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__CONTR_I__0FCC5F4C] DEFAULT (0) FOR [CONTR_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_Contr_Cancella1__82] DEFAULT ('N') FOR [Contr_Cancellation]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF__tblCall__CALL_RC__10C08385] DEFAULT ('N') FOR [CALL_RCV_EN_IN]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_PRISM_ClaimCan2__82] DEFAULT ('N') FOR [PRISM_ClaimCancellation]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_Program_Option_ID] DEFAULT (1) FOR [Program_Option_ID]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_ReinspectTag] DEFAULT (0) FOR [ReinspectTag]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_RandomNum] DEFAULT (rand()) FOR [RandomNum]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_BilledClient] DEFAULT (0) FOR [BilledClient]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_BilledContractor] DEFAULT (0) FOR [BilledContractor]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_billedAssign] DEFAULT (0) FOR [billedAssign]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_IsDirty] DEFAULT (1) FOR [IsDirty]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_roofInspectionFlg] DEFAULT (0) FOR [roofInspectionFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_contrEmailSentFlg] DEFAULT (0) FOR [contrEmailSentFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD CONSTRAINT [DF_tblCall_THDSentFlg] DEFAULT (0) FOR [THDSentFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [CATAssignment]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [RvwFlg]
GO
ALTER TABLE [dbo].[tblCall] ADD DEFAULT ((0)) FOR [PendingCancellationFlg]
GO
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2015 at 9:02 am
The following code archives and deletes records. But it does not contain a while loop to limit the batch size for the number of records that are committed.
Any ideas?
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.dbo.Orders
SELECT *
FROM dbo.Orders
WHERE OrderDate < @CutOffDate
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.Orders', 16, 1)
RETURN -1
END
INSERT INTO Archive.dbo.OrderDetails
SELECT *
FROM dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @CutOffDate
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.OrderDetails
WHERE OrderID IN
(
SELECT OrderID
FROM dbo.Orders
WHERE OrderDate < @CutOffDate
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.OrderDetails', 16, 1)
RETURN -1
END
DELETE dbo.Orders
WHERE OrderDate < @CutOffDate
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from dbo.Orders', 16, 1)
RETURN -1
END
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2015 at 9:09 am
How is this?
Can you see how it could be Improved?
DECLARE @NextIDs TABLE(UniqueID int primary key)
DECLARE @ThreeYearsAgo datetime
SELECT @ThreeYearsAgo = DATEADD(d, -(3 * 365), GetDate())
WHILE EXISTS(SELECT 1 FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo)
BEGIN
BEGIN TRAN
INSERT INTO @NextIDs(UniqueID)
SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo
INSERT INTO [ISArchive].[dbo].[MyTable] (<Fields>)
SELECT (<Fields>)
FROM [ISAdminDB].[dbo].[MyTable] AS a
INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID
DELETE [ISAdminDB].[dbo].[MyTable]
FROM [ISAdminDB].[dbo].[MyTable]
INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID
DELETE FROM @NextIDs
COMMIT TRAN
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2015 at 9:21 am
Quick question, is ISArchive ever used and then how?
😎
August 3, 2015 at 9:27 am
Eirikur Eiriksson (8/3/2015)
Quick question, is ISArchive ever used and then how?😎
No it has not been used.
I need to create a table and add a bit flag to stop the looping as part of the WHILE Loop.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply