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]
