In part 1 of this series of blog posts, we started looking at how you can analyze database activity by looking at the contents of the transaction log backups. If you’ve followed the steps provided in the previous blog post, you should be able to create a worktable that contains the aggregated values of INSERTS, UPDATES and DELETES per transaction log backup. A sample output from the worktable may look something like this.

Continue processing all of the transaction log backups that you’ve collected based on the dynamic SQL generated from the production database. Once you’re done with the processing, you can then import the data in Excel to create graphs and charts of the transactions within the specified period. Here’s an example chart displaying the volume of INSERT transactions on a sample database. This data is from a former customer from way back 2011. Note the trend in the amount of INSERT transactions early on in the analysis.

You can start creating the charts for the UPDATE and DELETE transactions to complete your analysis.

The Caveats

While this is a great way to analyze database transactions without having monitoring tools, it’s not without flaws. First, the INSERT/UPDATE/DELETE transactions recorded in the transaction log contain more than the information you need. The data you got from the queries that process the transaction log records are a bit skewed. Second, you really need to have an understanding of what your database does and the overall schema. Some of the questions that you may ask your database developers (or vendors if it’s a third-party custom application) are the following:

• How sure are you that the INSERT transactions on your database only pertain to new sales generated?
• Does the database include logging and audit functionality that also inserts records on an audit table within itself?
• How do new sales transactions get added to the database?

These are some of the things that you need to understand in order to quantify the results of your analysis. Your responses to these questions will determine whether or not the data you got from the analysis are inaccurate and skewed. They’re good enough to give you a sense of the database activity but still inaccurate, nonetheless. Fortunately, the transaction log (including the transaction log backups) contains  the information you need. If you have the name of the table that is used to track sales information, you can start digging deeper into the transaction log records to be more specific. For now, let’s work on what we have to at least get a report of the analysis of database activities. Give the report to your boss and see  how he responds.

In a future blog post, we will look at digging deeper to find out the amount of INSERT, UPDATE and DELETE transactions for a specific table. Stay tuned.