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. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
RES: Delete Records of Alert Events
Complementing... I'm using: ARS Server: ARS 7.0.01 Patch 004 - Operational System: AIX 5.3 ARS User: ARS 7.0.00 Patch 001 - Operation System: Windows XP Database: Oracle 9i De: Action Request System discussion list(ARSList) em nome de Tadeu Augusto Dutra Pinto Enviada: qui 10/1/2008 10:59 Para: arslist@ARSLIST.ORG Assunto: 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___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
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: arslist@ARSLIST.ORG 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___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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
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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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
Re: Delete Records of Alert Events
Tadeu, You could schedule an Archive on the Server Event form and just delete records without putting them into an Archive form. Since you have a large number of records, you could do this in batches to delete smaller chunks at a time and this also could be done after hours... Mike -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Tadeu Augusto Dutra Pinto Sent: Thursday, January 10, 2008 7:13 AM To: arslist@ARSLIST.ORG Subject: RES: Delete Records of Alert Events Complementing... I'm using: ARS Server: ARS 7.0.01 Patch 004 - Operational System: AIX 5.3 ARS User: ARS 7.0.00 Patch 001 - Operation System: Windows XP Database: Oracle 9i De: Action Request System discussion list(ARSList) em nome de Tadeu Augusto Dutra Pinto Enviada: qui 10/1/2008 10:59 Para: arslist@ARSLIST.ORG Assunto: 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___ ___ 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
Re: Delete Records of Alert Events
I think my 6.3 install had an escalation to delete the records. The Escalation
ran daily so the daily delete was small.
It had a Run-If something like:
('Create Date' = ($TIMESTAMP$ - (30 * 86400))) OR (( 'Create Date' =
($TIMESTAMP$ - (4 * 86400))) AND ( 'Read' = X))
It did something like:
Application-Delete-Entry $SCHEMA$ $Request ID$
So if the user has not read the alert within 30 days or of it has been 4 days
since they read the alert it would do a delete.
Your problem is just the initial cleanup and you could truncate the T, H, and B
tables for Alert Events as an intial cleanup. You might do an export of the
records that should remain and then import them back after the truncates are
done.
Fred
-Original Message-
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]
On Behalf Of Kendhammer, Mike
Sent: Thursday, January 10, 2008 9:01 AM
To: arslist@ARSLIST.ORG
Subject: Re: Delete Records of Alert Events
Tadeu,
You could schedule an Archive on the Server Event form and just delete records
without putting them into an Archive form.
Since you have a large number of records, you could do this in batches to
delete smaller chunks at a time and this also could be done after hours...
Mike
-Original Message-
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED]
On Behalf Of Tadeu Augusto Dutra Pinto
Sent: Thursday, January 10, 2008 7:13 AM
To: arslist@ARSLIST.ORG
Subject: RES: Delete Records of Alert Events
Complementing...
I'm using:
ARS Server: ARS 7.0.01 Patch 004 - Operational System: AIX 5.3 ARS User: ARS
7.0.00 Patch 001 - Operation System: Windows XP
Database: Oracle 9i
De: Action Request System discussion list(ARSList) em nome de Tadeu Augusto
Dutra Pinto
Enviada: qui 10/1/2008 10:59
Para: arslist@ARSLIST.ORG
Assunto: 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.
___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Delete Records of Alert Events
...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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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
Re: Delete Records of Alert Events
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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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
