reduce audit table

[Cathy]

I have an audit table of which one of the columns is named [when] and
defined as datetime

somebody has neglected doing maintenance on this table for several years now
and it has grown to a huge proportion

I have a script that will delete entries older than a specified date. due to
the large amount of entries, it is recommended to only delete around one
months data at a time and to run this out of hours.

To save me having to go in every night and change the value every day and
then scheduling it to run that night, I thought I would try and write a
script that could be scheduled every night to delete one months worth of
data.
When only 6 months worth of data remain, the script must continue running on
a nightly schedule but then only delete data older than six months.

In order to do this I have

[Cathy]

oops. hit send before I was done

I have the following so far

--1 Gives date as first day of the month six months ago and returns
2012-11-01 00:00:00.000
declare @date_six_months_ago datetime
set @date_six_months_ago = REPLACE(CONVERT(varchar(8), ( DATEADD(month, -6,
CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME))), 102), '.', '') + '01'

--2 Gives date of oldest entry in data returns 2002-01-03 18:01:06.923
declare @oldest_entry datetime
SELECT MIN([when]) [when] FROM audit

Whilst above SELECT works, I struggle to get this into a set statement for
@oldest_entry datetime

If anybody could help me with this it will be appreciated.

C

Post by Cathy
I have an audit table of which one of the columns is named [when] and
defined as datetime
somebody has neglected doing maintenance on this table for several years
now and it has grown to a huge proportion
I have a script that will delete entries older than a specified date. due
to the large amount of entries, it is recommended to only delete around
one months data at a time and to run this out of hours.
To save me having to go in every night and change the value every day and
then scheduling it to run that night, I thought I would try and write a
script that could be scheduled every night to delete one months worth of
data.
When only 6 months worth of data remain, the script must continue running
on a nightly schedule but then only delete data older than six months.
In order to do this I have

[rpresser]

Post by Cathy
--2 Gives date of oldest entry in data returns 2002-01-03 18:01:06.923
SELECT MIN([when]) [when] FROM audit
Whilst above SELECT works, I struggle to get this into a set statement for
@oldest_entry datetime

declare @oldest_entry datetime
SELECT @oldest_entry = SELECT MIN([when]) FROM audit

[Erland Sommarskog]

DECLARE @dawnoftime datetime,
@onemonthlater datetime,
@sixmonthsago datetime

SELECT @dawnoftime = MIN([when]) FROM audit

SELECT @onemonthlater = dateadd(MONTH, 1, @dawnoftime)

SELECT @sixmonthsago =
dateadd(MONTH, -6, convert(char(6), getdate(), 112) + '01'

DELETE audit
WHERE [when] >= @dawnoftime
AND [when] < CASE WHEN @onemothlater < @sixmonthsago
THEN @onemonthlater
ELSE @sixmonthsago
END

As always, test before you put into production.

[Cathy]

Thanks Erland

Very helpful post from you

I struggled a little with your WHEN statement but converted to the following
in the end

DECLARE @oldest_entry datetime, @one_month_later datetime, @six_months_ago
datetime, @delete_before datetime
SELECT @oldest_entry = MIN([when]) FROM audit
SELECT @one_month_later = dateadd(MONTH, 1, @oldest_entry)
SELECT @six_months_ago = dateadd(MONTH, -6, convert(char(6), getdate(), 112)
+ '01')
SELECT @delete_before =
CASE
WHEN @one_month_later < @six_months_ago
THEN @one_month_later
ELSE @six_months_ago
END
SELECT @delete_before