Tej_7342 (11/5/2013)
There are 2.2 billion records in the table and we want to only keep 2 weeks data, as of now we are lagging behind so we want to delete data in smaller chunks first so that the logs don't get messed up.
Given that I might suggest a different approach entirely. Instead of trying to delete 2.18 billion rows out of 2.2 billion it is probably easier to insert the rows you want to keep into a new table. Then drop the current table and rename the new table.
Something like this:
select *
into MyCopy
from MyCurrentData
where SomeDate > DATEADD(week, -2, getdate())
drop table MyCurrentData
exec sp_rename 'MyCopy', 'MyCurrentData'
Make sure you script all the indexes/constraints prior to this because the select into will NOT bring over any indexes or constraints.
As with anything, make sure you try this on a test system first. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/