Improve the SQL Server Database Size Achievement

Size of the [TRANSACTION] database before deleting the records from the ETL.ETL_Job_Log table:

Database Name: [TRANSACTION] 

Table Name:  ETL.ETL_Job_Log

Daily SSIS (ETL) Package and task logs are stored in the ETL.ETL_Job_Log table.

 

BEFORE:

After deleting log records older than one year, the free space increased from 50 GB to 134 GB.

Finally, after clearing out the ETL Log tables:

AFTER:

 

A faster way to delete rows from the big table:

Create a view on the [ETL].[ETL_Job_Log] table.

CREATE VIEW [ETL].[ETL_job_log_id_ToBeDeleted]

AS

    SELECT TOP 5000000 *

    FROM [ETL].[ETL_job_log]

    WHERE StartDT < DATEADD(year, -1, GETDATE())

    ORDER BY StartDT;

Create an index on the StartDT table for better performance, as we are using this column for

ordering.

CREATE NONCLUSTERED INDEX [IX_StartDT] ON [ETL].[ETL_job_log]

([StartDT] ASC

) WITH(PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

GO

Then Delete the records using the view like below.

Delete [ETL].[ETL_Job_log_id_ToBeDeleted]

Author: Naresh Cherukuri