To keep the Exchange database healthy, it is important to make sure that online maintenance and online defragmentation are running properly, or are at least conforming to Microsoft’s recommendations. Ben shows how easy it is to automate the maintenance and monitor the defragmentation task.
Exchange database maintenance is an important part of keeping Exchange healthy. Exchange Server automates online maintenance tasks and runs them based on the schedule specified by the Exchange administrator.
The online maintenance tasks are:
- Purge mailbox database and public folder database indexes
- Maintain tombstones
- Clean up the deleted items dumpster
- Remove public folders that have exceeded the expiry time
- Remove deleted public folders that have exceeded the tombstone lifetime
- Clean up conflicting public folder messages
- Update server versions
- Check Schedule+ Free Busy and Offline Address Book folders
- Clean up deleted mailboxes
- Clean up reliable event tables
For detailed information on these tasks you can refer to Microsoft TechNet: Maintaining Mailbox Databases and Public Folder Databases
If one of these maintenance tasks is performed on a database then online defragmentation will be performed on that database.
If the online maintenance tasks are unable to complete in a single schedule window the tasks will be suspended and then resumed in the next window. In this way maintenance is guaranteed to eventually complete, however it is important to make sure that the scheduled maintenance windows are properly configured so that the maintenance tasks are able to complete regularly.
When scheduling online maintenance there are several guidelines to consider:
- Online maintenance should be scheduled for times when there is little activity on the database.
- Online maintenance must not run at the same time as a backup (online defragmentation cannot start while a backup is in progress).
- Online defragmentation should be able to complete at least once every two weeks.
- Online maintenance schedules for databases in the same storage group should not overlap (Microsoft recommends a 15 minute gap between maintenance schedules).
The default online maintenance schedule is nightly from 1am to 5am. To aid in customizing the online maintenance schedule, the Windows event log can be used to see how often online defragmentation is completing and Performance Monitor counters can be used to check the efficiency of online defragmentation. This data can be used to adjust the online maintenance schedule to give more or less time.
Event Log Entries for Online Defragmentation
There are five events relating to online defragmentation starting and stopping. The events are logged in the Application log with a source of ‘ESE’ and a category of ‘Online Defragmentation’.
Events 701 and 703 indicate a complete pass. In the case of event 703, completion of a resumed pass, the event text will include information about how long defragmentation took to complete, and how many times it was invoked.
To make it easier to check how often online maintenance completes the Exchange Management Console and a PowerShell script can be used to parse the Application log.
This script will search the event log for online defragmentation messages for each database on the server on which it’s run (or the clustered mailbox server if running on a cluster node) and return the amount of time taken to complete online defragmentation of each database.
# Script to check the status of Exchange database online defragmentation tasks
# Written by Ben Lye
#
# The script will parse the event log of the local machine looking for online
# defrag related messages. Messages are parsed to determine when online defrag
# last finished for each database and how long it took to complete.
#
# The script needs to be run on an Exchange 2007 mailbox server or mailbox
# cluster node If the script is run on a cluster node it should be the active
# node, or event log replication needs to be enabled (this is the default).
# The $records variable defines the number of events to retrieve from the log
# It can be increased or decreased according to the needs of a particular server.
# The script will run faster if fewer records are retrieved, but data may not be
# found for all databases.
$records = 10000
# Get the hostname
$hostname = Get-Content env:computername
# Check if the local machine is an Exchange mailbox server
$mbserver = Get-MailboxServer -Identity $hostname -ErrorAction SilentlyContinue
# Check if the local machine is a member of a mailbox cluster
$cms = Get-ClusteredMailboxServerStatus -ErrorAction SilentlyContinue
# Exit the script if the local machine is not a mailbox server or a CMS node
if (-not $mbserver -and -not $cms) {
Write-Host "The machine $hostname is not a server an Exchange mailbox server." `
-ForegroundColor Red
Write-Host "This script must be run on a mailbox server or mailbox cluster node." `
-ForegroundColor Red
break
}
# Determine the server name to enumerate the databases
if ($cms) {
# This server is a cluster node, the database server name is the name of the CMS
$dbserver = $cms.ClusteredMailboxServerName
} else {
# This server is a mailbox server - the database server name is the local hostname
$dbserver = $hostname
}
# Get the mailbox databases from the server
$mbdatabases = Get-MailboxDatabase -Server $dbserver `
| Sort-Object -Property Name
# Get the public folder databases from the server
$pfdatabases = Get-PublicFolderDatabase -Server $dbserver `
| Sort-Object -Property Name
# Create an array for the databases
$databases = @()
# Check if mailbox databases were found on the server
If ($mbdatabases) {
# Loop through the databases
ForEach ($mdb in $mbdatabases) {
# Create an object to store information about the database
$db = "" | Select-Object Name,Identity,EdbFilePath,DefragStart,DefragEnd, `
DefragDuration,DefragInvocations,DefragDays
# Populate the object
$db.Name = $mdb.Name.ToString()
$db.Identity = $mdb.Identity.ToString()
$db.EdbFilePath = $mdb.EdbFilePath.ToString()
# Add this database to the array
$databases = $databases + $db
}
}
# Check if public folder databases were found on the server
If ($pfdatabases) {
# Loop through the databases
ForEach ($pfdb in $pfdatabases) {
# Create an object to store information about the database
$db = "" | Select-Object Name,Identity,EdbFilePath,DefragStart,DefragEnd, `
DefragDuration,DefragInvocations,DefragDays
# Populate the object
$db.Name = $pfdb.Name.ToString()
$db.Identity = $pfdb.Identity.ToString()
$db.EdbFilePath = $pfdb.EdbFilePath.ToString()
# Add this database to the array
$databases = $databases + $db
}
}
# Retrieve the events from the local Application log, filter them for ESE messages
$logs = Get-EventLog -LogName Application -Newest $records | `
Where {$_.Source -eq "ESE" -and $_.Category -eq "Online Defragmentation"}
# Create an array for the output
$output = @()
# Loop through each of the databases and search the event logs for relevant messages
ForEach ($db in $databases) {
# Create the search string to look for in the Message property of each log entry
$s = "*" + $db.EdbFilePath + "*"
# Search for an event 701 or 703, meaning that online defragmentation finished
$end = $logs | where {
$_.Message -like "$s" -and ($_.InstanceID -eq 701 -or $_.InstanceID -eq 703)
} | select-object -First 1
# Search for the first event 700 which preceeds the finished event
$start = $logs | where {
$_.Message -like "$s" -and $_.InstanceID -eq 700 -and $_.Index -le $end.Index
} | select-object -First 1
# Make sure we found both a start and an end message
if ($start -and $end) {
# Get the start and end times
$db.DefragStart = Get-Date($start.TimeGenerated)
$db.DefragEnd = Get-Date($end.TimeGenerated)
# Parse the end event message for the number of seconds defragmentation ran for
$end.Message -match "total of .* seconds" >$null
$db.DefragDuration = $Matches[0].Split(" ")[2]
# Parse the end event message for the number of invocations and days
$end.Message -match "requiring .* invocations over .* days" >$null
$db.DefragInvocations = $Matches[0].Split(" ")[1]
$db.DefragDays = $Matches[0].Split(" ")[4]
} else {
# Output a message if start and end events weren't found
Write-Host "Unable to find start and end events for database", $db.Identity `
-ForegroundColor Yellow
Write-Host "You probably need to increase the value of `$records." `
-ForegroundColor Yellow
Write-Host
}
# Add the data for this database to the output
$output = $output + $db
}
# Print the output
$output
Microsoft recommends that in large organisations with large databases (150 - 200GB) and many storage groups (up to 20) on a single server online defragmentation should complete at least once every two weeks for each database. In smaller organizations with smaller databases it should complete more often.
In either case, if online defragmentation is completing within two days then it is probably safe to shorten the online maintenance window for the database. If defragmentation is not completing within 14 days the online maintenance window should be lengthened.
Performance Monitor Counters for Online Defragmentation
Exchange 2007 includes the following performance counters for monitoring online defragmentation:
- MSExchange Database ==> Instances\Online Defrag Average Log Bytes
- MSExchange Database ==> Instances \Online Defrag Log Records/sec
- MSExchange Database ==> Instances \Online Defrag Pages Dirtied/sec
- MSExchange Database ==> Instances \Online Defrag Pages Preread/sec
- MSExchange Database ==> Instances \Online Defrag Pages Read/sec
- MSExchange Database ==> Instances \Online Defrag Pages Re-Dirtied/sec
- MSExchange Database ==> Instances \Online Defrag Pages Referenced/sec
Exchange 2007 Service Pack 1 adds these two additional counters
- MSExchange Database ==> Instances \Online Defrag Pages Freed/Sec
- MSExchange Database ==> Instances \Online Defrag Data Moves/Sec
The two interesting counters are ‘Online Defrag Pages Read/sec’ and ‘Online Defrag Pages Freed/Sec’. These two counters can be monitored during an online maintenance window and the average values compared to determine if the window should be increased or decreased.
If the ratio of Pages Read:Pages Freed is greater than 100:1 then the online maintenance window can be decreased, if the ratio is less than 50:1 then the maintenance window should be increased, and if the ratio is between 100:1 and 50:1 there is no need to change the window.
To use these counters extended ESE performance counters must be enabled, which is done by adding a new registry value.
Note: Incorrectly editing the registry can cause serious problems that may require you to reinstall your operating system. Problems resulting from editing the registry incorrectly may not be able to be resolved. Before editing the registry, back up any valuable data.
-
Start the registry editor on your Exchange 2007 Mailbox server.
-
Locate the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ESE\Performance registry key.
-
Right-click Performance, select “New”, and then select “DWORD value”.
-
Name the new DWORD value “Show Advanced Counters”.
-
Double-click “Show Advanced Counters”.
-
In the “Value data” field, enter 1.
-
Close the registry editor.
Once the extended ESE performance counters are enabled Performance Monitor can be used to log the counter data to a file.
-
Start Performance Monitor on the Exchange 2007 mailbox server (or the active cluster node in a CCR cluster) by clicking Start -> Programs -> Administrative Tools -> Performance.
-
Expand “Performance Logs and Alerts” and select “Counter Logs”.
-
Right-click “Counter Logs” and select “New log settings”.
-
Enter a name for the new counter, such as “EDB Defrag”, and click “OK”.
-
Click the “Add Counters” button, and then select “MS Exchange Database ==> Instances” from the “Performance object” drop-down.
-
Select the “Online Defrag Pages Freed/sec” and the “Online Defrag Pages Read/sec” counters from the list, select the Information Store storage groups, and click the “Add” button.
-
Click “Close”
-
On the “Log Files” tab change the “Log file type” to “Text File (Comma delimited)” and uncheck “End file names with”.
-
On the schedule tab se the start time to a few minutes before the start of the next run of online maintenance and the end time to a few minutes after the end.
-
Click “OK” and then close Performance Monitor.
The next time online maintenance runs the performance counter data will be gathered. This data can then be analyzed by looking at the average ratio of pages read to pages freed.
The resulting CSV file will look similar to this:
To determine the Pages Read:Pages Freed ratio the data is averaged over the duration of the maintenance window, and the average Pages Read/sec is divided by the average Pages Freed/sec. This gives the number of pages freed/sec for every one page read/sec.
For example, if the average Pages Read/sec is 545, and the average Pages Freed/Sec 6, the ratio is 90:1 and the online defragmentation window is appropriately set.
Setting the Online Maintenance Window
The online maintenance window is configured per database and can be set using either the Exchange Management Console or the Exchange Management Shell.
To configure the maintenance window using the console:
-
Launch the Exchange Management Console.
-
Expand “Server Configuration” and select the “Mailbox” node.
-
Select the server where the database is located then select the database.
-
Right-click the database and select “Properties”.
-
Select a predefined schedule from the “Maintenance Schedule” list or to create a custom schedule, select “Use Custom Schedule”, and then click “Customize”.
-
Click OK to close the properties window.
Alternatively, use the Set-MailboxDatabase PowerShell cmdlet to set the maintenance window. This command will configure the maintenance window of the database “Mailbox Database” on server SERVER01 to run every day from 3am to 4am local time using the shell:
Set-MailboxDatabase -Identity ‘SERVER01\Mailbox Database’ `
-MaintenanceSchedule Sun.03:00-Sun.04:00, Mon.03:00-Mon.04:00, `
Tue.03:00-Tue.04:00, Wed.03:00-Wed.04:00, Thu.03:00-Thu.04:00, `
Fri.03:00-Fri.04:00, Sat.03:00-Sat.04:00
With a relatively small amount of monitoring and analysis it is reasonably easy to ensure that online maintenance is running effectively and efficiently, helping to ensure that your Exchange databases stay in good shape.
The techniques I’ve shown here should help you to check that online maintenance and online defragmentation are running optimally, or are at least conforming to Microsoft’s recommendations.
