question

Oleg has a winning [answer to a similar question here][1]. [1]: http://ask.sqlservercentral.com/questions/7079/advise-on-deleting-huge-amount-of-rows

As you already noted, deleting data will cause activity on the transaction log. I would suggest you look at a few things before going ahead with this. 1. Can you try a different solution to deleting. You could select the data you want to keep into a new table, drop the existing table, rename the new table. This may not be an option if you have foreign keys etc. or the table has to remain online while you do this. 2. Do you have SQL Server Enterprise Edition. If so, have you considered looking at partitioning? You could push the current table into a partition scheme, splitting the newest data (that you want to keep) into a separate partition. You can then delete the old data without causing concurrency issues. 3. If you don't like the two options above, or cannot use them, please take care to pre-size your transaction log file. Ideally you will test the delete on a test machine and find out how large your log file will be getting. 4. You say the table is 1.8 Billion rows. How many of these rows are within the last 6 months, and how many will be deleted? What is the table and index structure like? Do you have an estimated query plan that we could look at to offer some advice on that? 5. Will the table still be in use while the deletion is taking place, or do you have a maintenance window? Regardless of what you end up doing, deleting large amounts of data is best done in batches. The size of the batch is dependant upon a great many factors, some of which I have mentioned already. The most important part is TEST, TEST, TEST. This will give you an idea of how long you need to delete and how much this will impact your system as a whole. Good luck and let us know if you need more help.

Well, I think that in this situation, Mark's answer to the question which he linked (which was similar to this one) would be the best approach in your case. Here is why I think so: You have a table with 1.8 billion rows which has the data going back 4 years. Assuming that the inserts were relatively evenly distributed, and you need to keep data for last 6 month, you have to delete 87.5% of your records and keep 12.5%. This means that you have to delete 1.584 billion records, and this is not a good idea. Using Mark's idea, it would be far cheaper to insert 216 million records into new table, drop the original table and then rename the new table to the original name. Reapply the indexes, constraint etc as needed. Deleting 1.584 billion records would be a more difficult task to manage, considering the log bloat and indexes fragmentation it will cause. Suppose you have 100 bucks in one-dollar bills, and you need to buy something which cost $88. Certainly you would opt to shell out $12 and give the salesman the rest right? Inserting 216 million records is not an easy task either, but seems like a much better option. You can break it into several manageable pieces, like one month worth of records at a time, provided that you do it from earlier to later period to preserve the natural way of inserts. Oleg

With @WilliamD here. My recommendation based on these assumptions: * You got Enterprise Edition * You can sustain the non-availability of old data for a few minutes One Solution: * Recreate the table (table_new), the way it is now, partitioned by month. * Plan on copying over the the last 6 months worth of data. Approach batch operations (eg. 1 day at a time). * If you are running it today, use a stop date of (date = '10/27/2010') * Drop the old table. Ongoing: * Plan on switching out old partitions and truncate the data (sliding windows) * Ensure that you add new monthly partitions **before** you hit the new month

A common technique to age out old data is to swap an empty partition (table) for the data you want to delete. The SWITCH only changes meta data so the billion rows are not moved on the media. After they are switched you can backup and drop the table. There is some overhead dropping and recreating the clustered index but there are no deletes or inserting/moving of data.

    CREATE TABLE [dbo].[LargeTable](
    	[RecordDate] [datetime] NOT NULL,
    	[intCol] [int] NULL,
    	[charCol] [char](10) NULL
    ) ON [PRIMARY]
    
    INSERT LargeTable(RecordDate,intCol,charCol)
    VALUES('4/30/2009',1,'historical')
    ,('5/31/2010 23:59:59.997',2,'historical')
    ,('6/1/2010',3,'keep data')
    ,('10/28/2010',4,'keep data')
    
    -- drop your existing CLUSTERED index here.
    --DROP INDEX blah blah
    
    -- create table identical to your original large table, same columns,
    -- same column order, same column nullability, collation etc
    -- it must contain the same primary key contstraint,foreign key constraints,check constraints,
    -- non-clustered indexes, clustered index(we dropped ours above, however),
    -- computed columns etc as the original
    
    -- the original table cannot be referenced by a foreign key in another table. drop these then after
    -- the switch recreate them 
    
    -- the date column used for partitioning should be NOT NULL in both tables
    -- the new historical table must be empty and must be on the same filegroup
    -- as the original table
    CREATE TABLE [dbo].[LargeTableHistory](
    	[RecordDate] [datetime] NOT NULL,
    	[intCol] [int] NULL,
    	[charCol] [char](10) NULL
    ) ON [PRIMARY] -- same filegroup
    
    -- cluster our partition column
    CREATE CLUSTERED INDEX cidx ON [LargeTableHistory] (RecordDate)
    
    CREATE PARTITION FUNCTION [RecordDateRangePF] (datetime)
    AS RANGE RIGHT FOR VALUES ('20100601') -- save everything from 6/1/2010 and later
    
    CREATE PARTITION SCHEME [RecordDateRangePS]
    AS PARTITION [RecordDateRangePF]
    ALL TO ([PRIMARY])
    
    -- create interim clustered index to align table with partition scheme
    CREATE CLUSTERED INDEX cidx ON [LargeTable] (RecordDate) 
    ON [RecordDateRangePS]([RecordDate])
    
    -- do magick -- 
    ALTER TABLE [LargeTable] SWITCH PARTITION 1 TO [LargeTableHistory]
    
    SELECT * FROM [LargeTable]
    SELECT * FROM [LargeTableHistory]
    
    -- drop the interim clustered indexes 
    DROP INDEX [LargeTable].cidx
    DROP INDEX [LargeTableHistory].cidx
    
    -- recreate the real clustered index on original table
    CREATE CLUSTERED INDEX cidx ON [LargeTable] (intCol) ON [PRIMARY]
    
    -- backup first, then drop the historical data
    DROP TABLE [LargeTableHistory]
    -- clean up
    DROP PARTITION SCHEME [RecordDateRangePS]
    DROP PARTITION FUNCTION [RecordDateRangePF]
    
    -- obviously you wont do this :)
    DROP TABLE [LargeTable]