Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnHi John
You could use SET ROWCOUNT 100 before issuing the delete statement. i.e. to
delete in blocks of 100
DECLARE @.earlierstdate datetime
SET @.earlierstdate = getdate()-90
SET ROWCOUNT 100
delete [<table>] where date < @.earlierstdate
WHILE @.@.ROWCOUNT > 0
delete [<table>] where date < @.earlierstdate
SET ROWCOUNT 0
John
"John J. Hughes II" wrote:

> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
>|||John J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.

> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...[vbcol=seagreen]
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>|||Hi John
"John J. Hughes II" wrote:

> John,
> Guess I did not see it before, thanks. By the way the BOL says to use top
> in new development since ROWCOUNT will no longer be supported. If I
> understand correctly then it should be the following:
> delete top(100) [<table>] where data < @.earlierstdate;
>
TOP is only for SQL 2005.

> I was thinking of putting this in a JOB set to when idle Do you think th
is
> would cause too much thrashing or would it be better to put it as you do b
ut
> with a waitfor delay. It would be nice to allow it to run until the serv
er
> became busy and then exit until next idle.
> Something like:
> while(@.@.rowcount > 0 and @.@.cpu < (something)
> delete top(100) [<table>] where data < @.earlierstdate;
> FROM BOL:
> Important:
> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements i
n
> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
> INSERT, and UPDATE statements in new development work, and plan to modify
> applications that currently use it. We recommend that DELETE, INSERT, and
> UPDATE statements that currently are using SET ROWCOUNT be rewritten to us
e
> TOP.
>
How you implement it will depend on how long your quiet periods are and how
many records you are deleting. I would probably start of with a single job
but delting in batches of 5000 (say) and then see if it is an issue. If you
have a policy of only keeping n days then rather than doing that as a monthy
process I would do it more gradually (say daily or weekly).
Make sure you do this deletion before you re-index.
If you do manage to get the system onto SQL 2005 then I would look at using
table partitions.
There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't think
they will be useful to you.
HTH

> regards,
> John
>
John|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>
> TOP is only for SQL 2005.
>
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>
> John
>