Surprise in optimizing tables in SQL

Hi all, I optimized the tables through Navision Client 3.70 on an SQL DB and I’ve noted that, for example, the dimension of table 17 before the process was 1600, and after the process was 700. Good! But after 30 minutes about, the dimension returned to be 1600!! Consider that during that time no user accessed the DB, I was the only one logged in and I modified the codeunit 1 in order to block any access during the process. Does anyone has some idea about this strange behaviour? I have noted also that dimension of some tables changes from minute to minute! I really don’t understand it…no server activity, no user activity…boh? Thanks Marco

Hi, Where do you see number of records? It could be you see counted approx number of records which is not real number. Use query analyser and execute statement: SELECT count(*) FROM [dbo].[CRONUS International Ltd_$Ledger Entry Dimension]

I guess that you mean “Record Size” when you talk about dimension ?

Yes, sure, sorry for my english [:o], I was talking about Record Size Marco

The record size is calculated from the total page size occupied by the table divided by the number of records in the table. The table size comes from a query against sysindexes - run a profiler and you’ll see it. So if the record size is changing then either the record count is changing or the table size is.

I think that the behaviour was due to the property AutoShrink that was enabled. Disabling it (reccomended by the Navision manual) solved the problem. Marco