What am I doing wrong?
I have several tables, part of an extraction from a ERP system.
I extract the data daily and because of the size of the files, I extract only the last 3 years, delete 3 years on the SQL table and import the data.
I have noticed that the table size in the SQL server has been growing even if the actual count of rows stays with little growth. Over a two-month period, the table has grown by 10 folds. If I recreate the table and re-import the data, all is OK. What am I doing wrong?
A1 There is most probably nothing being done 'wrong' as it were, (or at least nothing that regular table maintenance won't address satisfactorily).
1. To get an idea of what is occurring with the active table, run the following (in the DB containing 'Mottys_ActiveTable'):
dbcc ShowContig (Mottys_ActiveTable) With TableResults, All_Indexes, All_Levels
To see similar results for the remaining tables and indices run:
dbcc ShowContig With TableResults, All_Indexes, All_Levels
2. Run the applicable dbcc maintenance commands at regular intervals (as appropriate for the results obtained with ShowContig) to get a better idea of how often 'Mottys_ActiveTable' will need maintenance to best meet maintenance / performance requirements / objectives.