Purge data | Archive Data

Purging / Delete 

The Process of cleanig old data, which is not Required.
old data - Historical data

Need to consider below thing ,before proceeding Purging 

--------------------------------------

Check Dependency
Trigger 
Replication
Foreign key - cascade on delete
index
constraint-disable
Space-Reclaim
Shrink file
Rebuild Table
Frequency

Transaction Log

--------------------------------------

Delete From DailyMilkCollection where Date_Time>getdate()-180

batch wise data delete 

Delete top (100) From DailyMilkCollection where Date_Time>getdate()-180

While (@@ROWCOUNT>0)

BEGIN

Delete top (1) From DailyMilkCollection where Date_Time>getdate()-180

END

Delete top (100) From DailyMilkCollection where Date_Time>getdate()-180

While (@@ROWCOUNT>0)

BEGIN

Delete top (100) From DailyMilkCollection where Date_Time>getdate()-180

option (MaxDOP 1)

END

--Transaction Log file size

Data Archival 

Move Data from one table to another table (Archival Table) 
Data - old Historical data may be we will require it future.
Current Table size is big, in GB.

CREATE TABLE [dbo].[ArchiveDailyMilkCollection](

[TransId] [bigint] NOT NULL, ------imp

[Code] [bigint] NULL,

[CustomerID] [bigint] NOT NULL,

[FATValue] [numeric](8, 2) NOT NULL,

[SNFValue] [numeric](8, 2) NOT NULL,

[Quantity] [numeric](8, 2) NOT NULL,

[Rate] [numeric](8, 2) NOT NULL,

[Amount] [money] NOT NULL,

[MilkTimeID] [bigint] NOT NULL,

[MilkTypeID] [bigint] NOT NULL,

[Date_Time] [datetime] NOT NULL,

[Issettled] [bit] NULL,

[Iscancelled] [bit] NULL,

[AddedBy] [bigint] NULL,

[UpdatedBy] [bigint] NULL,

[Date] [datetime] NULL,

[Time] [datetime] NULL,

[ArchiveDateTime] [datetime] NOT NULL Default getdate(),---imp

CONSTRAINT [PK_ArchiveDailyMilkCollection_TransId] PRIMARY KEY CLUSTERED 

(

[TransId] ASC

)

)

GO

Create Procedure Archive_DailyMilkCollection

AS

BEGIN

BEGIN TRY

Create table #temp (TransId Int)

Insert Into #Temp([TransId])  

Select Top 100 [TransId]   

From DailyMilkCollection

where Date_Time<getdate()-180

While (@@ROWCOUNT>0)

BEGIN

BEGIN Transaction T1

INSERT INTO [dbo].[ArchiveDailyMilkCollection]

   ([TransId]

   ,[Code]

   ,[CustomerID]

   ,[FATValue]

   ,[SNFValue]

   ,[Quantity]

   ,[Rate]

   ,[Amount]

   ,[MilkTimeID]

   ,[MilkTypeID]

   ,[Date_Time]

   ,[Issettled]

   ,[Iscancelled]

   ,[AddedBy]

   ,[UpdatedBy]

   ,[Date]

   ,[Time])

Select     D.[TransId]

   ,[Code]

   ,[CustomerID]

   ,[FATValue]

   ,[SNFValue]

   ,[Quantity]

   ,[Rate]

   ,[Amount]

   ,[MilkTimeID]

   ,[MilkTypeID]

   ,[Date_Time]

   ,[Issettled]

   ,[Iscancelled]

   ,[AddedBy]

   ,[UpdatedBy]

   ,[Date]

   ,[Time]  

From DailyMilkCollection D

Inner join #Temp T On D.TransId=T.TransId

option (MaxDOP 1)

Delete D

From DailyMilkCollection D

Inner join #Temp T On D.TransId=T.TransId

option (MaxDOP 1)

Commit Transaction T1

Truncate Table #Temp

Insert Into #Temp([TransId])  

Select Top 100 [TransId] 

From DailyMilkCollection

where Date_Time<getdate()-180

END

END TRY

BEGIN CATCH 

Select ERROR_MESSAGE(),ERROR_LINE(),ERROR_NUMBER()

Rollback Transaction 

END CATCH

Call Procedure

Exec Archive_DailyMilkCollection

Sample Code- in one call

Delete top (100) DailyMilkCollection

output

deleted.TransId,

deleted.customerid

        ----all columns / Required columns need to mention.

into ArchiveDailyMilkCollection(TransId,CustomerID)

where Date_Time<getdate()-180