Huge IMINVTRX file

[kstickler]

Anyone using SQL scripts to deal with huge transaction tables? We haven't been able to maintain a purging plan and now the IMINVTRX file is huge.

The normal process of purging via spooling the Trx Hist By Item report takes hours and then the Export-Init-Import is another huge chunk of time.

I really want to just copy the current file over to a backup db and then purge all but the last 12 months data from the live file. Can the last 12 month extraction be done using SQL so we can quickly get back online instead of scheduling 12 - 24 hours of downtime?

 

[dgillz]

How big is it? Also please confirm you are talking about Microsoft SQL not Pervasive SQL.

Sure you can make a copy of the table and run a simple SQL script to purge it:

DELETE FROM iminvtrx_sql
WHERE trx_dt<20081116

I cannot say how much faster this will be.



Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[NEmacGuy]

Careful purging the file with SQL. There are trx's in the file that you don't want to delete such as allocations and on order qty's. You could drop them, then run the reset processes from inventory. I have seen one account that deleted them, then got error that inventory could not be allocated when posting invoices. Funny thing was other people I have seen do it with no issue. Reset fixed the issue.

 

[kstickler]

dgillz...yes it is MS SQL. The file is 2.8gb...I know it's really bad.

NEmacGuy...this is exactly my concern...plus, what about beginning balance records? I've run the reset processes before so that part is no big deal if it will resolve potential issues.

 

[dgillz]

That is not so huge, the biggest I have seen is 12gb. the whole database is 59gb.

If you are having performance problems, I would check your indexes. There is a utility program to do this called MSLSysCheck.exe. Your Macola business partner or Exact regional office should be able to get it for you.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html