An introduction to Log Shipping Monitor

Log Shipping Monitor is absolutely free, and no sign-up is required, but there's also no technical support.

Log Shipping Monitor

The Log Shipping Monitor is a free tool for collating and summarising SQL Server Log Shipping activity.

The Log Shipping Monitor provides information and customisable alerts for a variety of key log shipping variables, allowing you to take proactive steps to avoid any log shipping failures. In the event a log shipping process should fail, the tool can be used to quickly identify possible causes of the failure.

The Log Shipping Monitor uses .NET Framework v2.0 or later (32-bit download, x64 download)

Please post any questions and feedback on the forum.

Getting Started with Log Shipping Monitor

Registering a Log Shipping Group

To use the Log Shipping Monitor, the first task is to register one or more log shipping groups to monitor. Each group can consist of one production database and up to four associated standby databases.

To create a group, first click on the "Add group" button at the top of the screen (alternatively, click the "File" menu and select "Add log shipping group...").

LSM_setup02.png LSM_setup01.png

The first step of creating a new Log Shipping group is to fill in the required group settings:

  • Description - a unique name used to refer to the group.
  • Monitor interval - how often the application will retrieve information on the log shipping activity, in minutes.
LSM_setup03a.png

The second step is to fill in the "Production database" tab (all settings are required except for "Backup job" and "Job Step"):

  • Server - the name of the server and instance containing the production database, using standard SQL Server notation - i.e. SERVER\instance
  • Database - the name of the production database used for log shipping. If the Log Shipping Monitor can connect to the SQL Server instance, this list will be pre-populated with all recovered databases.
  • Connect using - select either 'Windows Authentication' or 'SQL Server Authentication'; in the case of 'SQL Server Authentication' the 'Login name' and 'Password' can be entered in the boxes provided.
  • Backup job - The SQL Server Agent job that is used to perform the backup.
  • Job Step - The step of the SQL Server Agent job that performs the actual log backup.
LSM_setup03.png

The third step is to fill in the "Standby databases" tab with one to four standby servers - to add a new standby server click "Add" (all settings are required except for "Restore job" and "Job Step"):

  • Server - the name of the server and instance containing the standby (destination) database, in the standard SQL Server notation - i.e. SERVER\instance
  • Database - the name of the standby database used for log shipping. If the Log Shipping Monitor can connect to the server, this list will be pre-populated with all non-recovered (standby or recovering) databases on the instance.
  • Connect using - select either 'Windows Authentication' or 'SQL Server Authentication'; in the case of 'SQL Server Authentication' the 'Login name' and 'Password' can be entered in the box below.
  • Restore job - The SQL Server Agent job that is used to perform the restore. This will be used to populate the "Restore duration" graph on the Summary view.
  • Job Step - The step of the SQL Server Agent job that performs the actual log restore.
LSM_setup03b.png

To save the settings, click the "Save" button. Any problems will be listed on the "Errors" tab.  To discard the settings, click "Cancel".

LSM_setup04.png

Once you have created your group, it will appear in the list on the left hand side of the screen.

LSM_groupsnalerts01.png

Summary View

The "Summary" tab provides an overview of what is happening for each log shipping group, both on the production database and associated standby database(s).

The top row provides a summary of activity on the production database, from left to right:

  • The database status and information about backups created in the last 7 days.
  • The size of backups over the last 7 days in graphical form - the blue line indicating the range of backup sizes.
  • The duration of bacukps over the last 7 days, in graphical form - the blue line indicating the range of backup duration.
LSM_summary01.png

Subsequent rows provide a summary of activity on each standby database in turn.  Each row contains, from left to right:

  • The database status and information about pending and actual restores during the last 7 days.
  • Details of the next transaction log backups that need to be restored. If more than 10 transaction log backups need to be restored, only the first 10 will be listed.
  • The durations of restores over the last 7 days, in graphical form - the blue line indicating the range of restore duration.  Note that the standby server must have a "Restore Job" listed for this information to be displayed.
LSM_summary02.png

Production Database View

The "Production database" tab provides detailed information about the log shipping activity happening on the production database. The information is split into four sections (from top to bottom) - Server and database properties, Available Drive Space, Backup Size Information and Backup Duration Information.

The Server and database properties row provides the following, from left to right:

  • Information about the SQL Server instance containing the production database, including version and collation settings.
  • Information about the production database used for log shipping, including the time of the last full and log backup.
  • Information about the last transaction log backup file, including size, times and LSN information.
LSM_production01.png

The Available Drive Space row provides:

  • The available free space available on all local or fixed drives on the server.
  • The size of the production database's files in relation to the free space available on the drive.
LSM_production02.png

The Backup Size Information row provides:

  • The size of backups performed in the past 24 hours, as a line graph.
  • The size of backups performed in the past 7 days, as a scatter graph.
  • Comparisons between the sizes over the past 24 hours and 7 days, as a line graph.
LSM_production03.png

The Backup Duration Information row provides:

  • The duration of backups performed in the past 24 hours, as a line graph.
  • The duration of backups performed in the past 7 days, as a scatter graph.
  • Comparisons between the durations over the past 24 hours and 7 days, as a line graph.
LSM_production04.png

Standby Database View

Each "Standby Database" tab provides detailed information about the log shipping activity happening on each individual standby database. The information is split into two sections (from top to bottom) - Server and database properties and Available Drive Space.

The Server and database properties row provides the following, from left to right:

  • Information about the SQL Server instance containing the standby database, including version and collation settings.
  • Information about the standby database used for log shipping, including the time of the last full and log restore, and the number of outstanding restores pending.
  • Additional Information about the log backups that need to be restored, up to the first 10 in sequence.
LSM_standby01.png

The Available Drive Space row provides:

  • The available free space available on all local or fixed drives on the server.
  • The size of the standby database's files in relation to the free space available on the drive.
LSM_standby02.png

Groups and Alerts

Each group can be monitored for a variety of different alerts - warnings or information about undesirable situations occuring in the log shipping process, such as backups and restores taking too long, or the disk running out of space.

To create an alert, right click on the group and select "Edit Group", which will bring you back to the "Setup Log Shipping Group" window. Selecting the "Alerts" button will display an area for entering custom alerts.

LSM_alerts01.png

The alerts that can be created (one per line), are as follows:

Keyword and Syntax Functionality
FREESPACE {drive letter}, {space in MB}

where:
{drive letter} is the fixed drive letter;
{space in MB} is the minimum expected drive space in MB.
This keyword will raise an alert if the drive in question contains less free disk space than expected.
Works with both the Production database and Standby Databases.

For example, to raise an alert when the E drive has less than two gigabytes of space remaining, use FREESPACE E, 2048.
LASTBACKUP {backup type}, {interval}

where:
{backup type} denotes the type of backup - D for a full backup, L for transaction log backups;
{interval} denotes the maximum expected time since the last backup - in seconds (by default), minutes (suffixed with an M), hours (H) or days (D).
This keyword will raise an alert if no backups of the selected type have been performed in the expected period.
Works only with the Production database.

For example, to raise an alert when the last transaction log backup was over 30 minutes ago, use LASTBACKUP L, 30m.
LASTRESTORE {restore type}, {interval}

where:
{restore type} denotes the type of restore - D for a full restore, L for transaction log restore;
{interval} denotes the maximum expected time since the last restore - in seconds (by default), minutes (suffixed with an M), hours (H) or days (D).
This keyword will raise an alert if no restores of the selected type have been performed in the expected period.
Works only with Standby Databases.

For example, to raise an alert when the last full restore was over 7 days ago, use LASTRESTORE D, 7d.
LOGBACKUPDURATION {duration}

where:
{duration} is the maximum expected time for the transaction log backup to take - in seconds (by default), minutes (M), hours (H), or days (D).
This keyword will raise an alert if any transaction log backup takes longer than the supplied value.
Works only with the Production database.

For example, to raise an alert when the last transaction log backup takes longer than 5 minutes, use LOGBACKUPDURATION 5m.
LOGRESTOREDURATION {duration}

where:
{duration} is the maximum expected time for the transaction log restore to take - in seconds (by default), minutes (M), hours (H), or days (D).
This keyword will raise an alert if any transaction log restore takes longer than the supplied value.
Works only with Standby Databases.

For example, to raise an alert when the last transaction log restore takes longer than 150 seconds, use LOGRESTOREDURATION 150.
LOGBACKUPSIZE {size in bytes}

where:
{size in bytes} is the maximum expected size of the transaction log backup, in bytes.
This keyword will raise an alert if any transaction log backup is larger than the supplied value.
Works only with the Production database.

For example, to raise an alert when the last transaction log backup exceeds 35MiB (36,700,160 bytes), use LOGBACKUPSIZE 36700160.
RESTORESPENDING {number of restores}

where:
{number of restores} is the maximum number of outstanding restores expected.
This keyword will raise an alert if any standby database has more transaction log backups outstanding than the supplied value.
Works only with Standby Databases.

For example, to raise an alert when any standby server has more than 12 pending restores, use RESTORESPENDING 12.

* Keywords and parameters are not case-sensitive, "FREESPACE", "freespace" and "FrEeSpAcE" are all equivalent.

Once you have entered the custom alerts, click "Save", and the alerts will become active.

When one or more custom alerts are raised, they will appear in the alerts panel. Alert messages that are in red have not yet been acknowledged or "cleared" (cleared messages will appear in black). The text to the right of the alerts panel provides details of why the selected alert was raised.

LSM_groupsnalerts02.png

Alerts can be cleared or deleted individually or in groups. To clear or delete all the alerts, right click the alerts panel and select "Clear all alerts" or "Delete all alerts" respectively. To clear or delete a subset of the alerts, select using the mouse (with the shift or control keys as required), then right click and select "Clear selected alerts" or "Delete selected alerts".

LSM_groupsnalerts03.png

Additionally when the application is minimised, an will appear in the system tray summarising the status of the registered log shipping processes (a red cross denotes that 1 or more alerts have not been cleared). To view additional details, hover over the system tray icon; alternatively double-click the to restore the application to the screen.

LSM_intro01v2.png LSM_intro02v2.png

To investigate which groups have outstanding alerts, look for a red cross on the left hand list of registered groups.

LSM_groupsnalerts01.png