One suggestion on the TRUNCATE TABLES, ensure that ARServer is SHUTDOWN because of the possibility that it will be creating records in that particular table while you are executing the sql commands.
You do not want to crash your server (possibly) or your user tool/alert tool errors because of "Missing H or B table records" We have used the Truncate Table many times without any issues, however, also take a backup of your DB prior to execution. Also use the "four eyes" (two people) to validate there are no typo's in the table_number (the schema ID returned from ARSchema). You would hate to truncate a wrong table... On Jan 10, 2008 8:19 AM, Heider, Stephen <[EMAIL PROTECTED]> wrote: > ...this got me thinking. It would be useful to see the number of records > in each of Remedy's data forms. > > I just created the attached SQL Server View that lists row counts of just > the data tables (T, H, B) and their corresponding form names. To see this > inside of Remedy, create a Remedy View form of this SQL View. > > HTH > > Stephen > Remedy Skilled Professional > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [EMAIL PROTECTED] On Behalf Of Heider, Stephen > Sent: Thursday, January 10, 2008 8:59 AM > To: [email protected] > Subject: Re: Delete Records of Alert Events > > Tadeu, > > The form on my server had 500,000 records. However, with the TRUNCATE > TABLE command the RDBMS handles the deletions differently than a DELETE FROM > command (which is what the Remedy clients use). > > The DELETE FROM command updates the Transaction Log so in the event of an > error it can Rollback the deletions. When you want to completely empty a > table then you certainly would not want to use the DELETE FROM. You could > end up with a 7GB increase in the size of the Transaction Log and it would > take forever because, in your situation, it would need to re-write all 35 > million records to the log. > > The TRUNCATE TABLE command wipes out every record in a table instantly and > discards all the data in one command. The Transaction Log is not updated > which is why it runs so quickly. > > I have no reservations at all with running this command in a production > environment - so long as you get the table names correct :) However, this > is your environment not mine. If you are a little unsure of performing this > operation during regular hours (although it should not matter with this > command on this particular Remedy form) then you could clear out these > entries off-hours after performing a backup of the database. > > HTH > > Stephen > Remedy Skilled Professional > > > -----Original Message----- > From: Action Request System discussion list(ARSList) [mailto: > [EMAIL PROTECTED] On Behalf Of Tadeu Augusto Dutra Pinto > Sent: Thursday, January 10, 2008 8:46 AM > To: [email protected] > Subject: RES: Delete Records of Alert Events > > Hi Stephen, > > Thank you very much for your post... > I did the test on my 'test server'... that I have only 2000 records .... > it's runs very well... > > But, it's safe to execute this command in my 'production server'?? Alert > Events on this server has 35.626.325 records... > > How many records did you delete from your system when you did it? > Around 35 million, too?? > > > Thankx-in-advance, again... > > Regards, > > > > Tadeu Augusto Dutra Pinto > ----------------------------------------------------------------- > IT Web Services ATM > Cinq Technologies > http://www.cinq.com.br > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > Fone: 41 3018-2833 - Cinq > Fone: 41 2107-5736 - HSBC Outsourcing > ----------------------------------------------------------------- > Confiabilidade, Inovação e Qualidade em T.I. > > ________________________________ > > De: Action Request System discussion list(ARSList) em nome de Heider, > Stephen > Enviada: qui 10/1/2008 11:17 > Para: [email protected] > Assunto: Re: Delete Records of Alert Events > > > ** > > Tadeu, > > > > I dealt with this very thing yesterday. Here is what I did, and it did > not affect the server. > > > > Deleting all these entries from a Remedy client will take forever and may > not even complete. Use the TRUNCATE TABLE command (SQL Server) to > instantly clear out all entries for this form. > > > > To do so, find out the table names that comprise the Remedy form. Run > the following command to get the schema number. > > > > SELECT schemaId > > FROM ARSchema > > WHERE name = 'Alert Events' > > > > On my system it is 6. Then run these three commands to clear out all > entries (substitute the number 6 for the number on your system). > > > > TRUNCATE TABLE T6 > > TRUNCATE TABLE H6 > > TRUNCATE TABLE B6 > > > > Now that the form is clear add workflow to automatically maintain the form > size. Add a display-only radio button field to the form named something > like 'Delete This Record'. Add a filter that runs on Modify when this > field = 0. The filter will use a Run Process with the command > Application-Delete-Entry "$SCHEMA$" $1$ to delete the record. > > > > Create an escalation that runs once a night which sets the value of 0 to > the 'Delete This Record' field when 'Create Date' < ($TIMESTAMP$ - (86400 * > 21)) This will keep only that last 21 days of entries in this form. > > > > > > Stephen > > Remedy Skilled Professional > > > > ________________________________ > > From: Action Request System discussion list(ARSList) [mailto: > [EMAIL PROTECTED] On Behalf Of Tadeu Augusto Dutra Pinto > Sent: Thursday, January 10, 2008 8:00 AM > To: [email protected] > Subject: Delete Records of Alert Events > > > > Hello for all, > > > > How can I clean up records from a table that growths abnormaly?? > > > > I'm facin' a serious problem with "Alert Events" table. > > Since 2004 it's been growing without control... Nowadays, this table has > around 7 GB... > > > > I want to know if have a way that I can delete all records of this table > with a security mode (not crashing the server)... > > > > Could it be with Escalations? > > If Yes, how? With Application-Delete-Entry? > Application-Query-Delete-Entry?? > > > > I thought in a way to delete records based in a range of date... Like > taking the date of First record saved in this table and going to deleting > month by month (in a range of a month/day)... > > > > Is it possible?? > > Gimme your suggestions, please! > > > > Thankx-in-advance... > > > > Regards... > > > > P.S.1: I have knowledge tha exists the Escalation "CleanUpAlertEvents" but > I think that this way will crash my ARS server if I runs it... > > P.S.2: I have another tables like that... but Alert Events is the > greater... > > > > > > > > Tadeu Augusto Dutra Pinto > > ----------------------------------------------------------------- > > IT Web Services ATM > > Cinq Technologies > > http://www.cinq.com.br > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > > Fone: 41 3018-2833 - Cinq > > Fone: 41 2107-5736 - HSBC Outsourcing > > ----------------------------------------------------------------- > > Confiabilidade, Inovação e Qualidade em T.I. > > __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" > html___ __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers > Are" html___ > > > _______________________________________________________________________________ > 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" > > > _______________________________________________________________________________ > 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"
