The audit database can become much larger than the main database and it does become necessary to archive the audit.
It is necessary to set a date before which the archive will be performed so that, for example, if it is set to 6 months any audit records within the last six months will not be affected and will stay in the system.
This does not remove any of the core data, only the audit of who has made changes to the data. For example, if a Works Order is raised by user A and modified by users B and C then there will be three lines of data in the audit. Archiving the audit will remove the lines saying that it was raised/modified by the three users but the WO itself will not be affected.
In addition, there are two options which will determine what happens to the archived data. The data can be deleted completely or archived off to a CSV file where it can still be accessed from Excel.
Setting up the Archive process
First, go to the main audit page.
From the Main Menu, press Ctrl-A.
At the bottom left of that screen click on the Archive button. That will bring up a new screen.
Clicking on Archive Ages allows different default retention times can be set.
The archive will be preserved within FM for different parts of the system if required, but this can be set globally in the Archive Options.
Choosing the ’Archive Options’ menu option from the menu above brings up a new menu.
Options
Default Data Age - this is where the setting of how far back the archive will be kept within FM as a default which can be changed.
Archive Options - The choices are Export archived data to CSV or Delete records.
Delete options means the audit records are removed from the system. If the audit data is required, then exporting to a CSV file would be the better option to choose. It will export records to external CSV files and then deletes the audit records from the system. Press F5 in the Export File field, to select the relevant file location where the CSV files will be exported.
Running the Archive
To run the archive choose the run option:
A new screen will then be shown and any changes required can be made prior to clicking on the "Run Now" button at the bottom.
There will be a warning message such as the following (this one is used when the export option is chosen).
When running through the archive, click on the screen, and a screen will show, whether it is actually complete or not.
This gives the chance to pause the process and come back to it later.
If the deleting option is chosen a new screen is shown.
When it is totally complete a message is shown after retrying.
The details of where it has put the data are shown in the next message.
The first part of the process is then complete. For the second part of the process, it is advisable this is completed by somebody proficient with SQL (IT department). Only proceed with the below if you are confident to do so.
Shrinking the database
The second part of the process is to shrink the files on the audit archive now that they have been extracted.
Connect to the SQL Server Management Studio using SA credentials,
Expand Databases
Select the desired database that needs to be shrunk e.g. FMAST12_A
Right-click on the database, Select Tasks - Shrink - Files
The shrink file dialog box appears.
We recommend you select the option: 'Reorganize pages before releasing unused space and Shrink File to desired size in MB (e.g. 3 MB).
This will shrink the database to the suggested size if you leave the number in. Or you can shrink it too it`s smallest size by replacing the number with the minimum number.
Amend the AutoClose option on all the FMAST and FMAST_PLAY databases to False via properties from SQL management studio.
You can also shrink the the transactional log files.
Note: Everybody can continue using FactoryMaster as usual, however the application may run a little slower while the process is completed. Also, it may lock some users out of the system.