I am new to this forum and have combed through various threads regarding the issue with the WMSESSION table getting huge.
We are running IS 7.1.2. in a single setup environment (i.e. there is no cluster, just one instance of IS).
As I understand there is an automatic way to archive audit data from WMSESSION and the like, but we haven’t made use of that feature yet.
Therefore the WMSESSION table has now grown to more than 20 million records, which I cannot delete efficiently without pulling the database machine down to the ground.
Neither thread I have found on this topic answered my question clearly, so here it is
Once IS is shut down, is it safe to TRUNCATE the table WMSESSION to get it emptied completely?
When IS is not running, I guess, it wouldn’t need any session info anymore as it cannot rely on the sessions still being valid once it starts up again, right?
Second question: When IS archives data from WMSESSION, what does it do with it and how does it remove it from the database (by DELETE FROM WMESSSION WHERE AUDITTIMESTAMP < …)?
Please help as our customer asks for a quick solution to free database space.
Something like that. All the archive/delete functions are in the WmMonitor package. What really happens behind the scenes? There is a convoluted stored procedure that the archive services call. To me it’s a toss up between MWS and the archive stored procedure for winner of the worst thing webMethods ever rolled out. :eek:
There is also a extended server setting that will let you turn off logging of session data as well, it’s in the admin guide.
There is no explicit requirement to shut down IS to archive WmError, you can opt for any of the option DB stored procedures/IS Service/MWS config to arhive SERVER/SERVICE/PROCESS schema’s.
We had our customer TRUNCATE the table during a regular maintenance interval. After the next startup IS was running fine and has ever since. So no side effects. I wouldn’t think, though, that it is safe to truncate the table while IS is running. While it’s down, no problem.
Not so sure : you have to check first if this table is not took as reference for other ones. If it’s the case and if you truncate it, you will mess wM’s purge procedure and other tables will contains lot of orphaned data. I don’t remember about WMSESSION but for such it worth opening an SR vs SAG on the same …
Do you really need archiving ?
If not, simply put “DELETE” as purge service parameter.
FYI, we had to fight during 6 months vs SAG support before having all our DB correctly purged because :
purge service are incomplete and “forget” some transaction
stored procedures are not solid as a rock, so if one is failing in the middle of the process, for whatever reason … you can be sure it will create orphaned data
stored procedures don’t report errors (you don’t known what was the error, and something they replied they were successful but … nothing has been purged).
To void that, I did a script that is checking the timestamp of table having this field and reporting for any data older than our threshold.
For other tables, I’m doing a graphical tracking of size trend …
However, this topic is long past This used to be an issue back in April and we resolved it by truncating the table in May. Since then we have a service running, which does a daily check for entries older than 30 days and deletes them. No problems at that end ever since.
Is the data purge suppose to takes care of tables other than tables that start with BAM_* like wmsession, wmerror, TBLEVENT? I have the following settings in MWS: Data Maintenance Settings:
Business Day to Retain: 7
Data Maintenance Interval: 4
Aggregated Business Days to Retain: 365
Process Tracker Settings for Analytic Engine:
Days To Retain Process: 7
I just run the Data Purge and the Operation Log display the following: END - All jobs have completed * Start Time: 2011-24-02 09:10:47 AM * End Time: 2011-24-02 12:25:51 PM * Total minutes: 195 * Tables included in rebuild 52: BAM_ALG_MONITOR BAM_DIM_JOIN_TMP BAM_DIM_TMP BAM_DIM_TMP_ATTR BAM_RULE_COMPLIANCE_FACT BAM_RULE_VIOLATION OPERATION_LOG BAM_AGG_ANALYTI_V1 BAM_AGG_ANALYTI_V11 BAM_AGG_ANALYTI_V12 BAM_AGG_ANALYTI_V13 BAM_AGG_FACTAGG_V1 BAM_AGG_GARBAGE_V1 BAM_AGG_INFLIGH_V1 BAM_AGG_KPISAND_V1 BAM_AGG_OPTIMIZ_V1 BAM_AGG_PERFORM_V1 BAM_AGG_PREDICT_V1 BAM_AGG_PREDICT_V11 BAM_AGG_PROCESS_V1 BAM_AGG_QUEUEME_V1 BAM_AGG_ROUTING_V1 BAM_AGG_STEP_ME_V1 BAM_AGG_TASK_ME_V1 BAM_AGG_TASK_SA_V1 BAM_AGG_WEBSERV_V1 BAM_FACT_ANALYTI_V1 BAM_FACT_ANALYTI_V11 BAM_FACT_ANALYTI_V12 BAM_FACT_ANALYTI_V13 BAM_FACT_FACTAGG_V1 BAM_FACT_GARBAGE_V1 BAM_FACT_INFLIGH_V1 BAM_FACT_KPISAND_V1 BAM_FACT_OPTIMIZ_V1 BAM_FACT_PERFORM_V1 BAM_FACT_PREDICT_V1 BAM_FACT_PREDICT_V11 BAM_FACT_PROCESS_V1 BAM_FACT_QUEUEME_V1 BAM_FACT_ROUTING_V1 BAM_FACT_STEP_ME_V1 BAM_FACT_TASK_ME_V1 BAM_FACT_TASK_SA_V1 BAM_FACT_WEBSERV_V1 BAM_PT_ERROR_INST BAM_PT_ERROR_STAGE BAM_PT_PROCESS_INST BAM_PT_PROCESS_INST_STATE BAM_PT_STEP_INST BAM_PT_STEP_STAGE BAM_PT_STEP_TRANS_INST
