Re: HUMONGOUS table
Hi, Truncating this with the API will take a lot of time. You could do this to truncate it completely: 1. Export Form-defs with directly related objects 2. Delete the form 3. Import Form-defs again You can also use RRR|Chive to truncate the table, and it chunks things automatically. If you have Linux/Solaris you can do it in one go, but the windows 32 bit binary you have to do it in chunks: This will export everything more than 7 years old. After that you can do 6, 5, ... up to the point you like. source_server = myserver source_user = Demo source_password = target_arx = auditdump.arx multipleforms = Audit Form qual= '3' $DATE$ - 7*365*24*60*60 splitsearch = YES transfertype= MOVE logfile = AUTO progressbar = YES Not that this will NOT result in a table scan. It will be slow, but there is nothing much you can do about that. And slow also means that the impact on the server is low. Best Regards - Misi, RR AB, http://rrr.se Thanks Ben! On Fri, Nov 16, 2012 at 12:14 PM, Ben Chernys ben.cher...@softwaretoolhouse.com wrote: Meta-Update will do it in any chunks you like. No need to build an API program when there's a simple way to use the API to do what you want. Trial licenses are unrestricted and are free. So you could clean the table free of charges :) Cheers Ben Chernys Senior Software Architect Canada / Deutschland Mobile: +49 171 380 2329GMT + 1 + [ DST ] Email: Ben.Chernys_AT_softwaretoolhouse.com Web: www.softwaretoolhouse.com We are a BMC Technology Alliance Partner Check out Software Tool House's free Diary Editor and out Freebies Section for a ITSM 7.6.04 and 8.0.0 Fields spreadsheet. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. http://www.softwaretoolhouse.com/ -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Longwing, LJ CTR MDA/IC Sent: November-14-12 16:50 To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table Warren, I can verify that he 1=2 nomenclature, as late as 7.6.04 triggers a table scan in SQL Server 2008. My recommendation would actually be to setup a new thread for the escalation server, and put an escalation on that new thread configured to cut the size of the audit log down to a manageable size, and let it run. The nature of the escalation server will make it so that the server will be busy, yes, but not so busy as to take it down, the fact that it's running on its own thread will mean that none of your other escalations will be impacted...it'll likely take many days to cut the numbers down, but it'll do it properly. The only other suggestion involves building an api app that'll do it in chunks 'periodically', but that may not be an option for you. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 8:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where
Re: ADV: RE: HUMONGOUS table
Thanks Ben! On Fri, Nov 16, 2012 at 12:14 PM, Ben Chernys ben.cher...@softwaretoolhouse.com wrote: Meta-Update will do it in any chunks you like. No need to build an API program when there's a simple way to use the API to do what you want. Trial licenses are unrestricted and are free. So you could clean the table free of charges :) Cheers Ben Chernys Senior Software Architect Canada / Deutschland Mobile: +49 171 380 2329GMT + 1 + [ DST ] Email: Ben.Chernys_AT_softwaretoolhouse.com Web: www.softwaretoolhouse.com We are a BMC Technology Alliance Partner Check out Software Tool House's free Diary Editor and out Freebies Section for a ITSM 7.6.04 and 8.0.0 Fields spreadsheet. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. http://www.softwaretoolhouse.com/ -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Longwing, LJ CTR MDA/IC Sent: November-14-12 16:50 To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table Warren, I can verify that he 1=2 nomenclature, as late as 7.6.04 triggers a table scan in SQL Server 2008. My recommendation would actually be to setup a new thread for the escalation server, and put an escalation on that new thread configured to cut the size of the audit log down to a manageable size, and let it run. The nature of the escalation server will make it so that the server will be busy, yes, but not so busy as to take it down, the fact that it's running on its own thread will mean that none of your other escalations will be impacted...it'll likely take many days to cut the numbers down, but it'll do it properly. The only other suggestion involves building an api app that'll do it in chunks 'periodically', but that may not be an option for you. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 8:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are -- Warren R. Baltimore II Remedy Developer 410-533-5367 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
We have noticed this as well, we have started using the criteria '1'=XYZ so it will hit the indexed EntryID field, made quite a difference on some of our big tables. Paul Campbell | Development Team Lead | TSD SSBL, A2R WFE, and ESP Remedy Team | Avaya Client Services | | 1145 Sanctuary Parkway Lake View II Suite 110 Alpharetta, GA 30009 | 678-421-5342 Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn't have deadlines, we'd stagnate. Walt Disney From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 10:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
I'll give that a try Then I just have to figure out a reasonable approach to cleaning this thing out! On Wed, Nov 14, 2012 at 10:08 AM, Campbell, Paul (Paul) p...@avaya.comwrote: ** We have noticed this as well, we have started using the criteria ‘1’=”XYZ” so it will hit the indexed EntryID field, made quite a difference on some of our big tables. ** ** Paul Campbell *|* Development Team Lead *|* TSD SSBL, A2R WFE, and ESP Remedy Team *| *Avaya Client Services *|** * *|* 1145 Sanctuary Parkway Lake View II Suite 110 Alpharetta, GA 30009 *|* 678-421-5342 ** ** *Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn’t have deadlines, we’d stagnate. Walt Disney*** ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Warren R. Baltimore II *Sent:* Wednesday, November 14, 2012 10:04 AM *To:* arslist@ARSLIST.ORG *Subject:* HUMONGOUS table ** ** ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ -- Warren R. Baltimore II Remedy Developer 410-533-5367 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
Warren, I can verify that he 1=2 nomenclature, as late as 7.6.04 triggers a table scan in SQL Server 2008. My recommendation would actually be to setup a new thread for the escalation server, and put an escalation on that new thread configured to cut the size of the audit log down to a manageable size, and let it run. The nature of the escalation server will make it so that the server will be busy, yes, but not so busy as to take it down, the fact that it's running on its own thread will mean that none of your other escalations will be impacted...it'll likely take many days to cut the numbers down, but it'll do it properly. The only other suggestion involves building an api app that'll do it in chunks 'periodically', but that may not be an option for you. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 8:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
1=0 (or any other always FALSE result) was no longer needed as of ARS 5.1.x. That is when I started removing all the 1=0 triggers. A quick and easy method to knock down the table is to use Oracle directly. I use TOAD and right click to rebuild the table. When TOAD builds the SQL for you, edit it and add in a reasonable where clause (Yes you can even use a WHERE 1=0 clause). This way you will have a T12345 and a T12345x table (so you don't lose data). You could then move records from the old x table over as you need to. This method should take about 10 seconds. Something else you should also do (I think 6.3 had this) is to turn off the Status-History on the Audit Trail (as records in it are only created and never modified). That drops you from 2 commits down to 1 for each entry. Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 9:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
How do you set the criteria, do you leave it blank? Paul Campbell | Development Team Lead | TSD SSBL, A2R WFE, and ESP Remedy Team | Avaya Client Services | | 1145 Sanctuary Parkway Lake View II Suite 110 Alpharetta, GA 30009 | 678-421-5342 Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn't have deadlines, we'd stagnate. Walt Disney From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Wednesday, November 14, 2012 11:11 AM To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table ** 1=0 (or any other always FALSE result) was no longer needed as of ARS 5.1.x. That is when I started removing all the 1=0 triggers. A quick and easy method to knock down the table is to use Oracle directly. I use TOAD and right click to rebuild the table. When TOAD builds the SQL for you, edit it and add in a reasonable where clause (Yes you can even use a WHERE 1=0 clause). This way you will have a T12345 and a T12345x table (so you don't lose data). You could then move records from the old x table over as you need to. This method should take about 10 seconds. Something else you should also do (I think 6.3 had this) is to turn off the Status-History on the Audit Trail (as records in it are only created and never modified). That drops you from 2 commits down to 1 for each entry. Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 9:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
Can't you systematically dump it to flat files, delete the period you just dumped and zip them and then keep going? Your version still has RUNMACRO. Try this: Directory Name \BMC Software\ARSystem\runmacro.exe -o Directory Name\Data\arx form name -x Server Name -U Demo -P password -f Form Name -t arx -a port name -q '3' ($DATE$ -(10*60*60*24*1)) Some delete mechanism Directory Name\BMC Software\ARSystem\runmacro.exe -o Directory Name\Data\arx form name -x Server Name -U Demo -P password -f Form Name -t arx -a port name -q '3' ($DATE$ -(9 *60*60*24*1)) . . . zip -r Direct or y Name\ All.zip Directory Name \Data\*.* If you use 8.0 you can use arexport. I may not have the syntax exactly right, but systematically you dump a years worth of data to a directory and then ZIP it. ARX or XML is a text file, so ZIP loves it. You can also run a macro which deletes each years worth of data as you dump it. The syntax is in the RUNMACRO description (-e I believe). I believe you used to be able to record a delete in a macro. You could probably write a Filter to delete records systematically using RUN Process Application-Delete-Entry formName entryID with some type of Table Loop. Also I believe if you set up an Archive, it will dump the data to a form (no indexes) and dump the Archive to a .arx or .xml. I would setup an Archive capability anyway so that it doesn't happen again. Gordon - Original Message - From: Warren R. Baltimore II warrenbaltim...@gmail.com To: arslist@ARSLIST.ORG Sent: Wednesday, November 14, 2012 10:04:03 AM Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
Leave the Push Fields criteria blank. The arserver process has logic in it so that if you are doing a push fields with no Push-If criteria and the push is set to If no Records match Create New Record / If any records match Take No Action then don't even check the database, just create a new record. Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Campbell, Paul (Paul) Sent: Wednesday, November 14, 2012 11:46 AM To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table ** How do you set the criteria, do you leave it blank? Paul Campbell | Development Team Lead | TSD SSBL, A2R WFE, and ESP Remedy Team | Avaya Client Services | | 1145 Sanctuary Parkway Lake View II Suite 110 Alpharetta, GA 30009 | 678-421-5342 Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn't have deadlines, we'd stagnate. Walt Disney From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Wednesday, November 14, 2012 11:11 AM To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table ** 1=0 (or any other always FALSE result) was no longer needed as of ARS 5.1.x. That is when I started removing all the 1=0 triggers. A quick and easy method to knock down the table is to use Oracle directly. I use TOAD and right click to rebuild the table. When TOAD builds the SQL for you, edit it and add in a reasonable where clause (Yes you can even use a WHERE 1=0 clause). This way you will have a T12345 and a T12345x table (so you don't lose data). You could then move records from the old x table over as you need to. This method should take about 10 seconds. Something else you should also do (I think 6.3 had this) is to turn off the Status-History on the Audit Trail (as records in it are only created and never modified). That drops you from 2 commits down to 1 for each entry. Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 9:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
Well, isn't that nifty, somehow I missed that little gem along the way Paul Campbell | Development Team Lead | TSD SSBL, A2R WFE, and ESP Remedy Team | Avaya Client Services | | 1145 Sanctuary Parkway Lake View II Suite 110 Alpharetta, GA 30009 | 678-421-5342 Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn't have deadlines, we'd stagnate. Walt Disney From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Wednesday, November 14, 2012 1:02 PM To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table ** Leave the Push Fields criteria blank. The arserver process has logic in it so that if you are doing a push fields with no Push-If criteria and the push is set to If no Records match Create New Record / If any records match Take No Action then don't even check the database, just create a new record. Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Campbell, Paul (Paul) Sent: Wednesday, November 14, 2012 11:46 AM To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table ** How do you set the criteria, do you leave it blank? Paul Campbell | Development Team Lead | TSD SSBL, A2R WFE, and ESP Remedy Team | Avaya Client Services | | 1145 Sanctuary Parkway Lake View II Suite 110 Alpharetta, GA 30009 | 678-421-5342 Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn't have deadlines, we'd stagnate. Walt Disney From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Wednesday, November 14, 2012 11:11 AM To: arslist@ARSLIST.ORG Subject: Re: HUMONGOUS table ** 1=0 (or any other always FALSE result) was no longer needed as of ARS 5.1.x. That is when I started removing all the 1=0 triggers. A quick and easy method to knock down the table is to use Oracle directly. I use TOAD and right click to rebuild the table. When TOAD builds the SQL for you, edit it and add in a reasonable where clause (Yes you can even use a WHERE 1=0 clause). This way you will have a T12345 and a T12345x table (so you don't lose data). You could then move records from the old x table over as you need to. This method should take about 10 seconds. Something else you should also do (I think 6.3 had this) is to turn off the Status-History on the Audit Trail (as records in it are only created and never modified). That drops you from 2 commits down to 1 for each entry. Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Warren R. Baltimore II Sent: Wednesday, November 14, 2012 9:04 AM To: arslist@ARSLIST.ORG Subject: HUMONGOUS table ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
There is nothing wrong with 57m rows as far as the db is concerned, you just need to be mindful of how you access that data. Take a look at your sql logs and see what the offending statement is that is causing the timeout, then update the workflow or form to address that issue. Then your problem is solved, you don't have to do anything with the data, and you are back in business. The update may consist of index or workflow modifications, or some combination thereof. Axton Grams On Wed, Nov 14, 2012 at 9:04 AM, Warren R. Baltimore II warrenbaltim...@gmail.com wrote: ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
Re: HUMONGOUS table
Yes, leave it blank. On Wed, Nov 14, 2012 at 11:46 AM, Campbell, Paul (Paul) p...@avaya.comwrote: ** How do you set the criteria, do you leave it blank? ** ** Paul Campbell *|* Development Team Lead *|* TSD SSBL, A2R WFE, and ESP Remedy Team *| *Avaya Client Services *|** * *|* 1145 Sanctuary Parkway Lake View II Suite 110 Alpharetta, GA 30009 *|* 678-421-5342 ** ** *Everyone needs deadlines. Even the beavers. They loaf around all summer, but when they are faced with the winter deadline, they work like fury. If we didn’t have deadlines, we’d stagnate. Walt Disney*** ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Grooms, Frederick W *Sent:* Wednesday, November 14, 2012 11:11 AM *To:* arslist@ARSLIST.ORG *Subject:* Re: HUMONGOUS table ** ** ** 1=0 (or any other always FALSE result) was no longer needed as of ARS 5.1.x. That is when I started removing all the 1=0 triggers. ** ** A quick and easy method to knock down the table is to use Oracle directly. I use TOAD and right click to rebuild the table. When TOAD builds the SQL for you, edit it and add in a reasonable where clause (Yes you can even use a WHERE 1=0 clause). This way you will have a T12345 and a T12345x table (so you don’t lose data). You could then move records from the old “x” table over as you need to. This method should take about 10 seconds. ** ** Something else you should also do (I think 6.3 had this) is to turn off the Status-History on the Audit Trail (as records in it are only created and never modified). That drops you from 2 commits down to 1 for each entry. ** ** Fred ** ** ** ** *From:* Action Request System discussion list(ARSList) [mailto: arslist@ARSLIST.ORG] *On Behalf Of *Warren R. Baltimore II *Sent:* Wednesday, November 14, 2012 9:04 AM *To:* arslist@ARSLIST.ORG *Subject:* HUMONGOUS table ** ** ** ARS 6.3 patch 16 ITSM 5.5 Oracle 10 Solaris I've got an audit trail table that has been quietly working for about 8 years now. We started seeing an issue about a month ago that is related to our AST:Asset table. Whenever a change is made and someone is associated with an asset, the system grinds to a halt. Usually, the change will timeout, but it will update. The problem is that at least once a day (usually in the morning) we will get a malloc error. For some reason, the server is not recycling itself when this happens so I have to do it. I've run all sorts of logs, and have come to the conclusion that it's the push field to the audit file that is causing the problems. The Filter was built using the old 1=0 trigger. I believe that this is triggering a table scan against the Audit Trail. The Audit Trail was never built to clean itself up and it has over 57 MILLION records! Anybody have any idea on a quick, easy, surgical method for knocking this thing down to a more manageable size without killing my server? Also, I know that in later versions, the need to use 1=0 went away. Any ideas if it was still neccesary in 6.3? I've tried the alternate method, but have not had success. Thanks in advance! -- Warren R. Baltimore II Remedy Developer 410-533-5367 ** ** ** ** _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ _attend WWRUG12 www.wwrug.com ARSlist: Where the Answers Are_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: Where the Answers Are
