I lift things up and put them down, Part 2.
OK, so it’s been a few weeks since our last session, it’s now time to give your database a physical examination, open wide!. Now, your database might be in good shape, but don’t let exterior fool you, we will need to run a Disk Usage by top tables report.
This report should give us good insight into the physical state of your database, it’s important that we learn how to maintain a healthy database form. We by all means should not just start trimming weight in all directions, this could have huge ramifications.
Ahh, looks like we just got your report back, hmm, very interesting. Just as I thought, you seem to be focusing too much on certain database parts. You need to start focusing on database tables that could lead to issues down the road, these tables usually don’t have an impact on your health right away, but slowly and surely you will start to notice that perfect database form slowly deteriorate. So, let’s prevent that some happening, here are the tables to keep an eye on, some will need more attention than others, while others you can monitor from time to time. One of the tables you will need to keep an eye on is Perform_action, this table records all types of data, this data should just be coming in and then out. Rows from perform_action should be cleared every 30 seconds, this is handled by get_worktobedone which is an Ektron stored procedure. For ideal situations, this table should be empty, but if its filling up and not clearing, then you should truncate this table (truncate table perform_action). A couple of reasons why this table would not be clearing:
Issue with the EktronWindowsServices40.
Access for the connection String user does not fall within Ektrons connectionString requirements
comments powered by
Assetservertable should only contain one entry if you just have a site connecting to a single database. The only time you should see more than one row is when configured for Ektron Load Balancing, you should see a row per every load balanced site. If you have rows within this table that contain incorrect server names, I would recommend deleting all rows (truncate table AssetServerTable) and stopping and starting the EktronWindowsServices40 for the correct cms server(s). You should now see the correct entire(s) within the assetservertable.
Now time to move onto the tables that don’t always need to be purged, but something to keep an eye on, as these can grow overtime. Content_history table will hold all the history for all the content within the workarea, to learn how to purge this data please click here. Another table to look out for is content_hits table, this table consists of cms analytic data. If you have data within this table that you no longer need, from lets say a year ago. You should be able to write a query that will truncate rows based on a given date. The last table an unknown table to most is scope_loginfo. This table will hold records of all previous eSyncs that have taken place in the workarea. I would use the same rule of thumb as with the content_hits table, delete rows within scope_loginfo that are 6 months to a year old.
As long as you maintain the database tables I mentioned above, you should be in good shape and a step closer to having that perfect database form of your dreams. I don’t always work out, but when I do, I lift things up and put them down.