Not that I recommend doing it at the DB level, but if you are going to go that route also ensure that you take care of the B table, as well as any additional B tables that may exist for each attachment field you may have on the form in question...but Joe, I wonder why you recommend resetting the next ID to 1? I agree that if you get all of the records, there is no reason to not do it...but why would you recommend it? One other thing...if you are going to delete with extreme prejudice such as that, isn't a truncate table t100 faster than delete from?
-----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Joe D'Souza Sent: Friday, January 25, 2008 9:18 AM To: [email protected] Subject: Re: List of Remedy tables to truncate? Elinore, If you delete data from these tables directly from the DB, you will not risk using and locking your AR Server threads whilst performing the delete action... So the timeouts a user might experience may not be significant (if your DB is a powerhouse) compared to timeouts they might experience if they are locked out of threads to use. Secondly, the delete operation will complete in much quicker time than doing it from the AR System User Client. To give you an example, if I were to delete a 100,000 records from the Sync Search database related tables, it takes me more than 5 minutes if I were to do it from an AR System User client.. The same operation I can complete within a few seconds if I were to do it directly at DB level. For doing this directly from the DB , find out the schema IDs for these tables by querying the arschema table by logging into any compatible SQL client to your DB such as SQLPlus, Toad, etc..: select name, schemaid from arschema where name like 'AR System Email %'; This will return names and the corresponding Schema Id of all forms whose names start with 'AR System Email '. If the schemaid for AR System Email Messages is 100, run these statements to delete data within this table.. delete from t100; delete from h100 update arschema set nextid = 1 where schemaid = 100; commit; If you are on Oracle you must run commit; after these transactions or it will not be auto committed to the database as that is the default setting on Oracle where auto commit is off. After running these, I would suggest that you create a procedure out of these commands if you need to perform this operation again. The next time you can simply use this procedure maybe even through an Escalation by running a Direct SQL periodically and calling that procedure.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Drew Shuller Sent: Friday, January 25, 2008 9:05 AM To: [email protected] Subject: Re: List of Remedy tables to truncate? Elinore, Feel free to delete the records in those tables. Crashing the server? It depends on how many records you'll be deleting, the size of your server, and how sensitive you are to user "timeouts," but I doubt you'll have a problem. Create a series of escalations that run after 6pm which each delete chunks of records. Then create more escalations that delete these records once a week or so. Drew Tulsa On Fri, 25 Jan 2008, Elinore AR wrote: > Hi All, > > We are doing some major clean-up on our old server (ARS 5.1) to unclog > our db and was looking into truncating records from the following > tables (that we have never used as reference for anything as far as I > know) > > - Alert Events > - AR System Email Messages > - AR System Email Error Logs > - AR System Email Attachments > > Any suggestions? Objections? Hesitations? Tips on how to not crash the > server while attempting to do this? > > Thanks in advance! > > Elinore No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.516 / Virus Database: 269.19.11/1243 - Release Date: 1/25/2008 11:24 AM ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
