October 18, 2011 at 12:23 am
Delete from t1 from account t1 join Resource t2 on t1.RID = t2.RID
Hi All,
The above query is deleting the similar columns in accounts table , it is not working, it is keep on running , and the log file is keep on growing, we have to delete 10 million of records.
but why it is keep on running
and log is increasing lot of size
but with the same querry it is working with the example table which we created for testing
plz help on this
Thanks in advance
October 18, 2011 at 6:55 am
trarunprasanna (10/18/2011)
Delete from t1 from account t1 join Resource t2 on t1.RID = t2.RIDHi All,
The above query is deleting the similar columns in accounts table , it is not working, it is keep on running , and the log file is keep on growing, we have to delete 10 million of records.
but why it is keep on running
and log is increasing lot of size
but with the same querry it is working with the example table which we created for testing
plz help on this
Thanks in advance
You could change it to a sub query so you would know how many records you are trying to delete.
This will give you the total number of records to be deleted:
select count(t1.rid) from account t1 join Resource t2 on t1.RID = t2.RID
delete from account where
rid in (select t1.rid from account t1 join resource t2 on t2.rid = t2.rid)
October 18, 2011 at 7:13 am
DELETEs are fully logged, and you're trying to delete 10 million rows IN ONE TRANSACTION. You need to break this up into more manageable pieces. You can use the TOP() clause to adjust the size of the batches.
DELETE TOP(10000) YourTable
FROM YourTable
INNER JOIN YourOtherTable
ON YourTable.[ID] = YourOtherTable.[ID]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 18, 2011 at 7:19 am
... and maybe increase log backups frequency while the process runs.
October 18, 2011 at 7:40 am
yes , you are correct , i check the row count it is reduced as per our requirement but , the query is keep on running and it keep increase the log size , in this log size is increasing 20 for 1 hour
October 18, 2011 at 7:45 am
Then run a log backup every 4-5 loops.
It'll get done eventually without killing the server.
The other option if you keep very little data is to export the data you want to keep to a new table. Drop the old table and rename the new one (make sure to keep indexes, constraints, triggers, etc.).
October 18, 2011 at 8:26 am
yes correct, the table have index, and SP's.
but my table is have the IDENTITY coloumn , it may reset? , for this it may cause any problem in feature .
October 18, 2011 at 8:56 am
SET IDENTITY INSERT dbo.table ON
Remember to put if back to off.
October 18, 2011 at 9:36 am
Excellent , Thank you very much , it is working fine :-):-) :-):-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply