deleting records older than 180 days

I have table with over 1 billion rows, which I'd like to purge by deleting
all records older than 180 days from the current date (using the getdate()
function). Any help here would be great. Thanks."Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.
If you have a datetime stamp in the table, then this is relatively simple.
DELETE <table name>
WHERE DATEDIFF( dd, <datetime column name> , GETDATE() ) > 180
If you don't have a datetime stamp in the table, then maybe one of your
related tables can give this information.
If you supply the DDL for your table and some sample data, we can help you
out with a better query.
See the following link for more information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA|||Well Rob, I'm assuming that you have a date field that you can use to
reference?
Assuming you have, the process is quiet simple, but depending on how many
rows you are trying to delete in one go, you may have problems with your
log files.
The syntax would be something like,
delete from table where YOURdatetimefield < (dd, -180, getdate())
Like i say, you should keep an eye on your log space and also, the first
time you run this process could take a while.
Immy
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||excuse the missing sytax...
delete from table where YOURdatetimefield < dateadd(d, -180, getdate())
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uvH7FD8OGHA.3944@.tk2msftngp13.phx.gbl...
> Well Rob, I'm assuming that you have a date field that you can use to
> reference?
> Assuming you have, the process is quiet simple, but depending on how many
> rows you are trying to delete in one go, you may have problems with your
> log files.
> The syntax would be something like,
>
> Like i say, you should keep an eye on your log space and also, the first
> time you run this process could take a while.
> Immy
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>|||Hope you have a date field in your table...
In that case, try
select * from tablename where DATEDIFF(day,DATEfield,getdate())>=180
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Rob" wrote:

> I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||In addition to the other replies about how to implement the date comparison,
keep the following in mind:
For performance reasons, you will probably want to delete the rows in blocks
of 10,000 rather than one large transactions.
http://groups.google.com/group/micr...br />
22014423
Rather than purging the data from your database, you may want to migrate the
data to another table and then join it using a partitioned view.
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||>For performance reasons, you will probably want to delete the rows in blocks
>of 10,000 rather than one large transactions.
>http://groups.google.com/group/micr...201442
3
Good point, JT.
When I have been faced with this sort of massive purge of old data in
the past I found it easiest to process a single day at a time, in a
loop, oldest to newest. This keeps the rows per DELETE down to a very
conservative number. It also allowed me to add a pause in there
(WAITFOR DELAY) to let the system do other things, and the log purge
(this was using what is now called the simple recovery model, where
logs are truncated). This approach also let me interupt it and
restart it later if I had to, since it was written to start with the
oldest date currently in the file, and cancelling only wasted the
current "day" it was working on.
Roy Harvey
Beacon Falls, CT|||Thanks JT.
Through several responses to my post, I was able to construct and parse the
appropriate delete stmt. I found your suggestion of deleting in batches to b
e
recommendable. However, I'm struggling with putting a viable criteria for th
e
WHILE clause in order for the loop to start and NOT continue infinitely.
Thanks again.
"JT" wrote:

> In addition to the other replies about how to implement the date compariso
n,
> keep the following in mind:
> For performance reasons, you will probably want to delete the rows in bloc
ks
> of 10,000 rather than one large transactions.
> http://groups.google.com/group/micr... />
6922014423
> Rather than purging the data from your database, you may want to migrate t
he
> data to another table and then join it using a partitioned view.
> http://www.microsoft.com/technet/pr.../2005/spdw.mspx
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>
>|||Hi Rob,
In the example below, the WHERE clause would simply need to reference the
DateEntered column. The "set rowcount 1000" statement limits each iteration
to 1000 or fewer rows, and what prevents an infinite loop is the statement
"if @.@.rowcount = 0 break". When no more rows are available for deletion,
@.@.rowcount will be 0 and the loop will be terminated with the "break"
statement.
set rowcount 1000
while
delete from mytable where DateEntered < '2005/10/22'
if @.@.rowcount = 0 break
checkpoint
end
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:EE27EBFD-428E-465F-9EC1-CA5E03700458@.microsoft.com...
> Thanks JT.
> Through several responses to my post, I was able to construct and parse
> the
> appropriate delete stmt. I found your suggestion of deleting in batches to
> be
> recommendable. However, I'm struggling with putting a viable criteria for
> the
> WHILE clause in order for the loop to start and NOT continue infinitely.
> Thanks again.
> "JT" wrote:
>sql