SQL Server Performance Forum – Threads Archive

Data Purge Transaction qs

Doing this for the first time and not a SQL Server DBA: I am looking at creating a scheduled process which purges data from around 10 tables in an ecomm system.
Steps being done – high level:
1.Create a job to delete data from tables.
2.Create delete triggers for all tables from which data is being deleted.
The delete trigger will insert values into an audit table with timestamp,tablename,etc.
3.If not successfull, rollback.
4.If successfull commit. However is it better to
1.purge data from all the 10 tables as a single transaction
or
2.Do each table data delete as part of individual transactions. This will be scheduled in the night on weekends probably. Not a high volume sight regularly. Also if someone could suggest any better process overall, would appreciate it. thx a lot…

Keep each table data in individual transaction rather than forcing all at once. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

It depends.
Do you have referential integrity enforced between the tables? Will it break if data is not purged from all tables at the same time? If one of the deletes fail but the rest are successful, do you need to roll back the others or continute to try to delete the rest? Can you determine which records to delete from each table without reading values from the other tables?
If its not an ‘all or nothing’ then I’d say individual txns for each table. If its all tables delete successfully or no tables have records deleted, then you can still do this in individual txns if you write the deleted records out to some tables. Then if one of the deletes fail, you can refill the other tables, figure out what went wrong and try again. If all are successful, you can drop the copies of the deleted records.
So the approach depends on the structure of your DB and the nature of your data. Chris

1. If CASCADING referential integrity is defined between some of your tables, utilize it to reduce the amount of actual tracking you have to do to make sure that all related records have been purged. 2. Exceptions thrown inside triggers will generally ROLLBACK the transaction that fired it. Yes, I would use the ON DELETE triggers to make sure that all other related deletes (even the nested ones) are rollback/committed. 3. Yes, an umbrella transaction for all the smaller transactions should not pose a problem since you are scheduling the job to run at times of no activity. If this is a public site available 24/7/365 then you will have a problem on your hands later due to the length of the long running transaction. You could have smaller enveloping transactions around ‘tightly related’ table deletes to mitigate this worry.
Nathan H.O.
Moderator
SQL-Server-Performance.com

]]>