Regularly we receive back-ups of Exact Globe databases to analyse problems or help speed up support issues. Very often, the size of these databases could be limited by cleaning up historical E-WMS log files.
First you need to know what SQL tables take up most of the space. When executing this SQL script (with thanks to the author mentioned in that script), stored procedure Usp_GetTableSizes will be created. You only need to run this script once per database. After that, you can run the command usp_gettablesizes in a new SQL query window. The output will be a list of tables in the current database, sorted by number of records descending.
When using E-WMS (or ICL, Pick-IT or Central Warehouse) some tables could contain a considerable number of records. These tables could be cleaned up periodically:
This table contains all messages generated by the E-WMS (or ICL) services and from actions within the control centers. Probably you do not need history for more then, let’s say, 30 days. In that case you could schedule in SQL a query to run each night, like this: DELETE FROM csPickitErrorReport WHERE syscreated < GETDATE()-30
That will delete all records older than 30 days. When executing the first time, this can take considerable time !
This table contains a log of all actions performed within control centers and on the hand terminals. You could for instance measure orderpicker productivity from the data in this table. If you do not need this data (or not that old), you can use the same method as decribed above (for csPickitErrorReport) to delete old records periodically.
- E-WMS ASP session history
When using E-WMS ASP (Internet Explorer or ERF as client) each screen and input on the hand terminals is logged into three session log tables. This is to provide insight in hand terminal sessions, or to check what wrong bar code or text was put in. This management screen can be accessed by using the URL http://myserver/myWMS/management (where myserver is your IIS server, and myWMS is your WMS web site name). At this time, this management screen only shows session information from Internet Explorer sessions, not from ERF sessions.When you do not need this session history, you can periodically clean up these session log tables (again saving last 30 days in this sample);
delete CSPickItWebFormFields from CSPickItWebFormFields c join CSPickItWebRequests b on c.WebRequestID=b.ID join CSPickItWebSessions a on b.WebSessionID=a.ID where a.[DateTime] < GETDATE()-30
delete CSPickItWebRequests from CSPickItWebRequests b join CSPickItWebSessions a on b.WebSessionID=a.ID where a.[DateTime] < GETDATE()-30
delete from CSPickItWebSessions where [DateTime] < GETDATE()-30