Analyzing Exchange Transaction Log Generation Statistics - Revisited


Almost 4 years ago, I wrote a script called GetTransactionLogStats.ps1, and originally blogged about it here. At the original time of writing, the primary purpose of the script was to collect transaction log generation data, and use that to determine the percentage of transaction logs generated per hour in an environment. This could in turn be used to populate the related fields in the Exchange Server Role Requirements Calculator.

Since originally writing this script, I’ve made some significant updates to how the script functions, and also added a handful of new features along the way, which were significant enough that I wanted to have a new post about it. Of note, the script now:

  • Uses PowerShell Remoting to collect data from many remote servers in Parallel. This significantly speeds up collection speeds.
  • Generates a database heat map, that compares the number of logs generated for each database to the number of logs generated for all databases. This can be used to identify databases that may be overloaded or underloaded.
  • Uses only log generation information from active database copies to determine log generation statistics.
  • Accepts the target servers via a script parameter instead of via a text file.

Requirements

The script has the following requirements;

  • Target Exchange Servers must be running Exchange 2010, 2013, or 2016
  • PowerShell Remoting must be enabled on the target Exchange Servers, and configured to allow connections from the machine where the script is being executed.

Parameters

The script has the following parameters:

  • Gather: Switch specifying we want to capture current log generations. If this switch is omitted, the -Analyze switch must be used.
  • Analyze: Switch specifying we want to analyze already captured data. If this switch is omitted, the -Gather switch must be used.
  • ResetStats: Switch indicating that the output file, LogStats.csv, should be cleared and reset. Only works if combined with –Gather.
  • WorkingDirectory: The directory containing LogStats.csv. If omitted, the working directory will be the current working directory of PowerShell (not necessarily the directory the script is in).
  • LogDirectoryOut: The directory to send the output log files from running in Analyze mode to. If omitted, logs will be sent to WorkingDirectory.

Usage

Runs the script in Gather mode, taking a single snapshot of the current log generation of all databases on servers server1 and server2:

PS C:\> .\GetTransactionLogStats.ps1 -Gather -TargetServers "server1","server2"

Runs the script in Gather mode, specifies an alternate directory to output LogStats.csv to, and resets the stats in LogStats.csv if it exists:

PS C:\> .\GetTransactionLogStats.ps1 -Gather -TargetServers "server1","server2" -WorkingDirectory "C:\GetTransactionLogStats" -ResetStats

Runs the script in Analyze mode:

PS C:\> .\GetTransactionLogStats.ps1 -Analyze

Runs the script in Analyze mode, and specifies an alternate directory to send the output files to:

PS C:\> .\GetTransactionLogStats.ps1 -Analyze -LogDirectoryOut "C:\GetTransactionLogStats\LogsOut"

Output File After Running in Gather Mode

LogStats.csv

When run in Gather mode, the log generation snapshots that are taken are sent to LogStats.csv. The following shows what this file looks like:

image

Output Files After Running in Analyze Mode

LogGenByHour.csv

This is the primary output file for the script, and is what is used to populate the hourly generation rates in the Exchange Server Role Requirements Calculator. It consists of the following columns:

  • Hour: The hour that log stats are being gathered for. Can be between 0 – 23.
  • LogsGenerated: The total number of logs created during that hour for all days present in LogStats.csv.
  • HourToDailyLogGenRatio: The ratio of all logs that that particular hour accounts for. The sum of values for this column in all 24 hours in the table should be 1, and can be copied directly into the Exchange Server Role Requirements Calculator.
  • NumberOfHourlySamples: The number of hourly samples that were used to calculate each hour value.
  • AvgLogGenPerHour: The average number of logs generated per database per hour.

image

LogGenByDB.csv

This file contains a heat map showing how many logs were generated for each database during the duration of the collection. This information can be used to figure out if databases, servers, or entire Database Availability Groups, are over or underutilized compared to their peers. It consists of the following columns:

  • DatabaseName: The name of the database being measured.
  • LogsGenerated: The total number of logs created by primary copies of that database during the duration of the collection.
  • LogGenToTotalRatio: The ratio of logs generated for this database compared to logs generated for all databases.
  • NumberOfHours: The number of hourly samples that were taken for this database.
  • LogsGeneratedPerHour: The average number of logs generated per hour for this database.

image

LogGenByDBByHour.csv

This file is similar to LogGenByDB.csv, but shows the log generation rates per hour for each database. It consists of the following columns:

  • DatabaseName: The name of the database being measured.
  • Hour: The hour that log stats are being gathered for. Can be between 0 – 23.
  • LogsGenerated: The total number of logs created by primary copies of that database during that hour.
  • HourToDailyLogGenRatioForDB: The ratio of logs generated for this hour and this database compared to the total logs generated for this database.

image

Running As a Scheduled Task

Since the script is designed to be run an hourly basis, the easiest way to accomplish that is to run the script via a Scheduled Task. The way I like to do that is to create a batch file which calls Powershell.exe and launches the script, and then create a Scheduled Task which runs the batch file. The following is an example of the command that should go in the batch file:

powershell.exe -noninteractive -noprofile -command “& {C:\LogStats\GetTransactionLogStats.ps1 -Gather -TargetServers “server1”,”server2” -WorkingDirectory C:\LogStats}”

In this example, the script is located in C:\LogStats. Note that I specified a WorkingDirectory of C:\LogStats so that if the Scheduled Task runs in an alternate location (by default C:\Windows\System32), the script knows where to find and where to write LogStats.csv. Also note that the command does not load any Exchange snapin, as the script doesn’t use any Exchange specific commands.

Mike Hendrickson

Comments (5)

  1. robk says:

    Hi Mike

    trying to run your script and get the following error

    Import-Csv : Cannot open file "C:\Output\LogStats.csv".

    1. Hi @robk. What's the full syntax you used to execute the script when you received that error? Prior to running in -Analyze mode, did you already run in -Gather 2 or more times, and verified that the LogStats.csv file was created?

      1. robk says:

        i think i know why, you need to run the gather option first, before running analyze. otherwise the script will error out

        thank you

  2. Some genuinely prize content on this web site, bookmarked.

  3. Guru says:

    Thank you for a very useful script. Also to mention, it requires minimum Powershell version 3.0 and Exchange 2010 will still have version 2.0.

Skip to main content