November 18, 2013 at 3:50 am
Hello,
We currently have an application database that contains several main tables which all contain approximately 3-4 million records. Currently the table has a flag which denotes whether a record is active or not (Logcially Deleted flag). As our database and these tables are growing on a daily basis we want to remove the records flagged as 'logically deleted' as there are about 100,000 in each table.
The problem is that the records in these tables have constraints set up to other various other tables. The Logically Deleted flag is set via a SP, however as this is a highly used database then I did not want to necessarily add a load of logic into the SP to check all of the other tables for related records and delete them as well (this logic would also need maintaining as new tables are added to the database).
I was looking into enabling CDC on the tables (as it is used elsewhere in the database) to capture the records when they are actually deleted (rather than just setting the logicallydeleted flag) and then enabling "Cascading Deletes" on certain constraints to tables where there could be related records (CDC will be enabled on these tables too). Does this seem like a viable solution to this issue? We have to keep deleted records but do not want them within the main tables anymore.
Most people seem scared off from using Cascading Deletes without actually trying them out? Did anyone have any good or bad experience of them? Should I avoid them and write all the logic for checking and deleting records myself? As always, time is limited, so am also looking for a quick (but accurate solution) that has minimal maintenance?
Thanks
November 18, 2013 at 6:27 am
I don't have any experience that I can share with you, but I can explain why I don't use it. The main reason that I don't like using cascading deletes is that I can delete by mistake one record and then find out that I've just deleted half of my database. Did you ever make a mistake and tried to delete the wrong data at the wrong environment? It did happen to me and the one thing that stopped it was the constraints that my operation violated. If I had cascading deletes, I would have done a lot of damage.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 18, 2013 at 6:40 am
Adi Cohn-120898 (11/18/2013)
I don't have any experience that I can share with you, but I can explain why I don't use it. The main reason that I don't like using cascading deletes is that I can delete by mistake one record and then find out that I've just deleted half of my database. Did you ever make a mistake and tried to delete the wrong data at the wrong environment? It did happen to me and the one thing that stopped it was the constraints that my operation violated. If I had cascading deletes, I would have done a lot of damage.Adi
Delete operations (as well as update) are always first tried on testing environment to ensure that the desired records are deleted (if you're not very sure, or if a someone sent you a deleting/updateing script).
To be more sure, you can replace the DELETE with SELECT into the deleting select to see what records are actually selected before continuing with the DELETE.
Cascade deleting is just a fine thing. You're sure that you don't have 'dirty' rows across the referencing tables.
Igor Micev,My blog: www.igormicev.com
November 18, 2013 at 6:43 am
I want to add that delete operations usually introduce a lot of fragmentation, so you should do something to reduce it.
Anyway, I think marking logically deleted records, and then later deleting all is a good approach.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 18, 2013 at 9:29 am
Thanks for your replies.
Cascading deletions do sound efficient and very low maintenance, however the risk of someone accidently running a delete statement on Produciton and the ramifications of that do sound scary...:w00t: We do have a group of people who query and support the database so the risk of this happenning is greater.
I think we may actually go for another route, whereby we keep using the logically deleted flag, however in our application database Release downtime, we can hard delete these (catching them in CDC) and then rebuild indexes if necessary straight after. This process can then be repeated if necessary whenever we have an application release (and the application that uses the database is down to users). This would also minimise the processing hit against the database as deletions (and the querying to find the related data) would only occur during the downtime and not when users are on the system.
Thanks
🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply