Hi,
what is in the sq_internal_msg table?
It seems pretty big (+600K rows) on a not so old system.
Cheers.
It contains the internal messages that Matrix sends, but also the log messages you see on the log screen of each asset.
There is a config screen in 3.10 (or a config file in other versions) that allows you to log less information to the DB if required.
You can also clear out logs if you need to.
[quote]It contains the internal messages that Matrix sends, but also the log messages you see on the log screen of each asset.
There is a config screen in 3.10 (or a config file in other versions) that allows you to log less information to the DB if required.
You can also clear out logs if you need to.
[right][post=“11978”]<{POST_SNAPBACK}>[/post][/right][/quote]
So, only log info.
Would you recommend -like rollback tables- to trim the table regularly?
[quote]Would you recommend -like rollback tables- to trim the table regularly?
[right][post=“11979”]<{POST_SNAPBACK}>[/post][/right][/quote]
That depends on your requirements. Government sites have legislative requirements that means they cannot trim this table (it contains the audit logs of all changes made to the system).
However, if you don’t need to keep those logs, you can erase it daily. 
[quote]That depends on your requirements. Government sites have legislative requirements that means they cannot trim this table (it contains the audit logs of all changes made to the system).
However, if you don’t need to keep those logs, you can erase it daily.
[right][post=“11980”]<{POST_SNAPBACK}>[/post][/right][/quote]
Well, we do rely on backup for the legislative requirements (rollback content).
I think we might end up truncating the table monthly or so.
The erase procedure would be a simple delete * from sq_internal_msg; or is there the need for a synchronisation in the same way rollback tables do it?
Note that this table only contains DUPLICATE data of what is contained in the system.log file, so deleting it will not get rid of logs forever. However, if you have modified your messaging config file, you could get the system to stop logging to the system.log and log to DB instead, in which case you'd be in trouble.
To clear it, you can truncate, but then all valid internal messages (eg. message sent to user ABC to tell them workflow has started) will also be deleted. If this is a problem, I think you can delete where "userto = 0", but I'm not sure if that will get them all. Someone would need to dig into the code a bit deeper.
[quote]Note that this table only contains DUPLICATE data of what is contained in the system.log file, so deleting it will not get rid of logs forever. However, if you have modified your messaging config file, you could get the system to stop logging to the system.log and log to DB instead, in which case you'd be in trouble.
To clear it, you can truncate, but then all valid internal messages (eg. message sent to user ABC to tell them workflow has started) will also be deleted. If this is a problem, I think you can delete where "userto = 0", but I'm not sure if that will get them all. Someone would need to dig into the code a bit deeper.[/quote]
Is there a best practice to clean up the sq_internal_msg table as ours is about 6 times bigger than the rest of the entire matrix database (makes our backups unecessarily huge)
can anyone recommend a maintenance script or things to do to prune some of the bloat from our system?
cheers Mat
Mat: checkout scripts/remove_internal_messages.php – it allows you to remove certain types of messages within a date range. I'd cron that puppy to remove any messages you don't need on a regular basis.
If the table gets too big, it will have some effect on performance.
As Greg has said, the system log is all replicated in system.log, so it is definitely worth using log rotate to break it up daily so you can archive it forever…
It's hard to manage the table, and delete unneeded (if you can even identify what is needed and not…there are too many different types of messages/ varied content for each message type) if you are required to keep track of changes. Depending on the situation you definitely do not want to log too little.