November 15, 2006 at 2:44 am
Hi all,
Could anyone elaborate on what this code does? I can see that it's basically a delete that checks the current date and I THINK - determines what age of data ought to be deleted, and then deletes it, but if someone could let me know what age of data gets deleted, that'd be great.
Cheers,
JB.
CREATE PROCEDURE Dugena_prune_data
AS
set nocount on
if not exists(select name from sysobjects where name = 'hold' and type = 'U')
begin
table.
select top 5* into hold from webproxylog
truncate table hold
create index [IDX_Hold_Date] on Hold([logDate]) with fillfactor = 100
end
if day(getdate()) = 7
begin
declare @day1 varchar(20)
select @day1 = dateadd(mm,datediff(mm,0,getdate()),0)
declare daycur cursor for
select distinct logdate
from webproxylog
where logdate >= @day1
order by logdate asc
open daycur
declare @date varchar(11)
fetch next from daycur into @date
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
-- Keep a record of the number of rows deleted for each day.
declare @count varchar(20)
select @count = count(*) from webproxylog where logdate = convert(varchar(11),@date,113)
insert into Audit (numbers,save_dates,delete_date) values(@count, convert(varchar(11),@date),default)
insert into hold select * from webproxylog where logdate = convert(varchar(11),@date,113)
end
backup log isalog with truncate_only
fetch next from daycur into @date
end
close daycur
deallocate daycur
truncate table WebProxyLog
create index [IDX_Hold_Date] on Hold([logDate]) with fillfactor = 100 ,drop_existing
declare impcur cursor for
select distinct logdate
from hold
order by logdate asc
open impcur
fetch next from impcur into @ddate
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
insert into WebProxyLog select * from hold where logdate = convert(varchar(11),@ddate,113)
end
backup log isalog with truncate_only
fetch next from impcur into @ddate
end
close impcur
deallocate impcur
return 0
end
else
begin
print 'Out Of Date'
return
end
GO
November 15, 2006 at 4:10 am
Here he is specify the age of data.
select @day1 = dateadd(mm,datediff(mm,0,getdate()),0)
This shows that the start of the current month...
Cheers
cheers
November 15, 2006 at 5:05 am
Thanks for that!!!
Jaybee.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply