MS SQL 2005 purging old data question

2b ... and if the log file cannot grow, then the delete will roll back.

Actually, in the future if you only want to keep 20 days of data, you may consider first copying the data  you want to keep to a new table for holding, then truncating the original table, then copying the data back from the holding table into the original table.  This will be faster and far less resource intensive than deleting the rows.

-- example:

select *
into my_holding_table
from my_table (nolock)
where mydate > '2010-09-01'
go

-- verify the data in the new table is correct, then do this:

truncate my_table
go

insert into my_table
select * from my_holding_table (nolock)

-- done

Are any of the server resources pegged (CPU/RAM/DISK)?

The ramification for stopping should be nothing other than lost time... assuming no transactions have been committed.  Restarting will probably not help you much.

When your log hits 100%, it will either
- grow if it is set to grow
- error if it is not
- error regardless of grow/no grow and you are out of physical disk space

Often is the case that you can do large operations in several smaller steps faster than in one big step.

You went from pruning > 180 days to > 20... 160 days could be a big difference from the norm (if you run it once per day... you're used to seeing 1 day of deletion)

I recommend you try an dividing up the delete job.

DANG, knightEknight....

I should have posted my answer in batches!! :)

Great minds think alike!  

(and so do ours)

:)

Yes the RAM pegged by sqlsvr.exe process because the db size is 20GB.  That's why I'm trying to shrink it.  

So if I stop the purge process all the deleted filed will roll back like nothing happened at all?

Thank you for recommendation in batches!  Now I know.

!

How did it go?  Much better broken down?

Nope.  Truncating and then shriking worked.  Purging did not help and never finished..