Task 2: Purging database process

• One of my last task was to create a purge procedure in order to delete all data older than 1 year. The Staging Area database was 50 GB wide. Building the procedure not took me a long time, but to execute it took longer because the transaction log for database it was full caused by using the delete statement. 
• A fancy way in build this procedure is to make the delete data as a configurable parameter and update the last purge date.

CREATE TABLE [dbo].[Parameter](
[Parameter_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](100) NOT NULL,
[Value] [varchar](500) NULL,
[DataType] [varchar](100) NULL,
[Description] [varchar](500) NULL,
[Created] [datetime] NOT NULL,
[CreatedBy] [varchar](100) NOT NULL,
[Modified] [datetime] NOT NULL,
[ModifiedBy] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED
([Parameter_ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]
GO

• It's pretty easy to write the delete statements when you work with Audit table: The Audit table is logging all the events that are occurring in the system. It contains the auditing messages of the processes/ operation involved: What type of process is started, what is the parent, ultimate parent, execute process date.  

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_PurgeData] AS

BEGIN

SET NOCOUNT ON;

DECLARE @RetentionNumberOfDays INT  

--How many days to preserve the data in the DB before purging it.

DECLARE @LastPurgeDate DATETIME  

--Last date when the purge process was completed.

SELECT  @RetentionNumberOfDays = CAST(VALUE AS INT) 

FROM dbo.Parameter P

WHERE P.Name = 'RetentionNumberOfDays'

SELECT @LastPurgeDate = CONVERT(DATETIME, VALUE, 111) 

FROM dbo.Parameter P

WHERE P.Name = 'LastPurgeDate'

DECLARE @DeleteStagingDate DATETIME

SET  @DeleteStagingDate = DATEADD("DAY",-1 * @RetentionNumberOfDays, getdate())

DELETE Z    

FROM  dbo.[Table1] Z WITH (TABLOCKX )

INNER JOIN dbo.Audit A ON Z.AuditID = A.AuditID

WHERE (A.ExecStart < @DeleteStagingDate)

UPDATE P 

SET VALUE = CONVERT(VARCHAR, GETDATE(),111)

FROM dbo.Parameter P

WHERE P.Name = 'LastPurgeDate'

END