April 22, 2003 at 12:02 pm
Hi
We have a huge table which has about 200 million rows and now we are inthe processes of archiving the data on date basis to another server so that Table A from Server 1 will move the data that is over 6 months old to Table A on Server2 and then delete the rows from TableA on Server1.
Now the question:
How do we best go about doing it without generating huge amounts of transaction log entried?
Is there a clause we can use for nologging the bulk inserts and/or bulk deletes.
Thanks for you response.
-Soumil
April 22, 2003 at 1:57 pm
There is not such bulk deletes in SQL Server. Delete large amount data will generte large transaction log entries but if you delete the records in batchs, for example, 10000 records a batch, and backup the log more frequently, you shouldn't have issue with transaction log file. Do reserver more disk spaces for transaction log backup files.
Edited by - Allen_Cui on 04/22/2003 1:58:21 PM
April 22, 2003 at 2:31 pm
How would you do break a huge statement like
delete From tableA
where LHS=RHS
If this tableA is 200 million rows and the delete will have 5 million rows affected how can u break this statement into smaller batches.
Thanks
-Soumil
April 22, 2003 at 2:39 pm
Example,
WHILE 1 = 1
BEGIN
SET ROWCOUNT 10000
DELETE FROM YourTable WHERE xxx = yyy
IF @@ROWCOUNT = 0
BEGIN
SET ROWCOUNT 0
BREAK
END
SET ROWCOUNT 0
BACKUP LOG ......
END
April 22, 2003 at 2:53 pm
Depending on the frequency of your log backups, you may want to include an explicit log backup after every x records/iterations. You may also want to 'pace' the delete so that it doesn't run the server at max effort - put in a waitfor to put a pause between deletes.
Andy
April 23, 2003 at 1:30 am
SELECT INTO is not logged (see BOL for details), and can be used to create and copy rows into the archive table.
You may also find it quicker to create a new table containing just the records you want to keep, rather than deleting the ones you don't.
Again, you could use SELECT INTO to do this.
Obviously number of rows, constraints etc may make this impractical, but it is worth considering.
I have used this technique many times, and it has huge performance advantages over doing the deletes.
April 23, 2003 at 3:38 am
However 5 million records is not a large enough percentage of the 200 million to make using SELECT INTO worth while. The reason is you are then building a table with 195 million rows which will far exceed what will happen to the log with 5 million deletes.
If however you were keeping only 5 million rows then SELECT INTO to create a table with the 5 million, truncating the orginal table and SELECT INTO the back would be a far better solution in my opinion.
Allen's method should work great for you, you might even consider temporarly setting Truncate on Checkpoint on or Simple recover if SQL 2000. This will allow the log to flush periodically and you could expressly by using the
CHECKPOINT
expression in the while loop to cause the truncate to occurr between delete sets.
April 29, 2003 at 6:42 am
you can use "TRUNCATE TABLE mytable", which is in fact a DDL statement (*not* DML) and therefore not logged at all.
that means that you won't be able to run it as part of a transaction and do a rollback if required.
but be careful, this won't work if foreign key constraints on that table are active.
best regards,
chris
April 29, 2003 at 7:59 pm
quote:
you can use "TRUNCATE TABLE mytable", which is in fact a DDL statement (*not* DML) and therefore not logged at all.that means that you won't be able to run it as part of a transaction and do a rollback if required.
but be careful, this won't work if foreign key constraints on that table are active.
best regards,
chris
Actually Chris you can use in a transaction and do a rollback. I too originally thought this and someone showed me otherwise. Give a test and see for yourself.
April 30, 2003 at 6:47 am
Truncate Table deallocates the table's data pages and index pages. The deletion of rows can not be logged, because they are not really deleted. The page deallocations are logged.
Patrick
Quand on parle du loup, on en voit la queue
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply