Thursday, June 16, 2011

What are the tables InventSumDateTable and InventSumDateTrans used for?

When browsing the AOT, you may come across these two tables: InventSumDateTable and InventSumDateTrans.

Using your table browser, you see that they are empty.  So what are they used for?

Both tables are used by the report Physical inventory by inventory dimension, which can be found under Inventory Management - Reports - Status - Physical inventory. 
This report takes you back in time, calculating the stock on hand of your items for any given date in the past. 
You might experience some performance isssues when running this report, as some calculations are needed.

The report uses both tables mentioned earlier, to store these calculations.  After generating the report, a cleanup is performed.  So if you should stumble on some left over data in these tables (the user wasn't patient enough and aborted), you can safely delete any data left in these tables.
Posted by Willy at 1:25 AM
Labels: ,

2 comments:

  1. Klaas DeforcheJuly 12, 2012 at 6:37 AM

    Thx for the info Willy, the information from MS about these tables is pretty limited.
    A customer of ours has 64m records in the InventSumdateTrans table... Are you sure it is safe to delete all these records?

    ReplyDelete
  2. AnonymousMarch 29, 2023 at 3:47 AM

    You can delete it
    We have a job that runs every night on the SQL Server which does this maintenance

    DELETE FROM [AXDataBase].[dbo].[INVENTSUMLOGTTS]
    WHERE [MODIFIEDDATETIME] < GETUTCDATE() - 45

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[INVENTSUMLOGTTS] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    DELETE FROM INVENTSUMDATETRANS

    GO

    DELETE FROM INVENTSUMDATETABLE

    GO







    /* REBUILD INDEXES TO AVOID LOCKING AND BLOCKING */

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[INVENTSUMDELTA] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[INVENTSUMDELTADIM] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[LEDGERBALANCESTRANSDELTA] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[BATCH] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[BATCHCONSTRAINTS] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[BATCHJOB] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[INVENTCOSTLISTTRANS] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[INTERCOMPANYINVENTSUM] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[INTERCOMPANYINVENTDIM] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[REQLOG] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[AIFRESOURCELOCK] REBUILD WITH (ONLINE = ON, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[AIFSCHEMASTORE] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[AIFGATEWAYQUEUE] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[AIFRUNTIMECACHE] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[REQPROCESSTHREADLOG] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[REQPROCESSLIST] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[SYSTRACETABLESQL] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[INVENTCLOSINGLOG] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ALTER INDEX ALL ON [AXDATABASE].[dbo].[AIFRESPONSE] REBUILD WITH (ONLINE = OFF, MAXDOP = 12);

    GO

    ReplyDelete

Subscribe to: Post Comments (Atom)