Delete Statement Problem
-
Hi
I have a table with a lot of data that needs to be deleted not all of it...This is Large amount of data more in size than in Qauntity of rows...The Delete has an inner join wich links to a nother table on n linked server..
I have done batches and subquery's in the delete but the thing just keeps on running throw the night...And I have to stop it in the morning cuase the transaction log makes the server slow...
Is there anayway I can do the delete without writing to the transaction log? Or just make this not take so long..?
-
What recovery model have you selected for your database? You should have Simple.
In Theory, theory and practice are the same...In practice, they are not. -
Yes the recovery model is on simple, but the problem here is thee amount of data there is a text field coulumn that is kinda big wich I think is the problem....Im all out of idees
..Thanx for the idee though -
bcp out the data you want to keep, truncate the table and bulk insert it back in.
-
Hi thanks for the reply
Cant truncate Foreign key constraints and I am not sure but Disabling the constraints has passed my mind but it just does'nt seem right..They are there for a reason?
-
The bcp will work but with the size of the data this will take just as long? This will be a daily process aswell
-
i think BCP is the speedest method of all.
In Theory, theory and practice are the same...In practice, they are not. -
I agree the bcp will be n good way to do this but when I bcp all the data out , the data that is left still has Foreign key constraints on it...You cant use Truncate when the table has Foreign keys so even if I did this it still would'nt work...Maybe im understanding the whole bcp idee wrong?
-
you should script it as stored procedure which would be run as daily job. Maybe in this script you should be aware about all constraints, how to drop them and to be rebuilt.
In Theory, theory and practice are the same...In practice, they are not. -
Thanx,
This is obvisaly a cant do then...I apricate all the reply's...Its alredy a job that runs every night but the problem is it runs forever and I cant let it run during the day cause its a live enviroment...Im trying not to drop or disable the constraints...What im looking for is an alternative way??...I supose if there is some why of duing this without writing to the Transaction log that would be it but I geuse there is no way of bypassing that one...
-
You could try smaller batches. make sure they are not in the same transaction tho... and try sticking a backup log in between each statement. if you don't want the logs use the with truncateonly.
-
Thanx Sounds good Im gona try that...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply