Possible data loss: SQL Server logs may be truncated even though the Data Protection/FlashCopy Manager for SQL transaction log backup failed

Flash (Alert)


Abstract

This document describes how data loss can occur after the backup of Microsoft SQL Server transaction logs, by Data Protection for Microsoft SQL Server or FlashCopy Manager for Microsoft SQL Server, fails and the /truncate=yes option was used for the backup.

Content

This document is divided into linked sections for ease of navigation. You may use the links below to jump to the desired section of the document.

Problem
Conditions Required for Problem to Occur
How Do I Know if I am Affected?
Workaround
Affected Products, Components, Releases and Fixing Levels

Further Assistance & Related APARs


Problem:

If:
  • Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server, or
  • Tivoli Storage FlashCopy Manager: FlashCopy Manager for Microsoft SQL Server
fails during the backup of the Microsoft SQL Server's transaction log (also known as a LOG backup), the Microsoft SQL Server's transaction log may be truncated even though the data was not saved by the backup process.

Since the backup of the log failed, and the data previously stored in the log has been truncated from that log, it will not be possible to use the Microsoft SQL Server's transaction log for recovery in the event of an SQL server crash or failure.

Also, the failed LOG backup, plus any future LOG backups created prior to the next FULL or DIFFERENTIAL backup, can not be used to restore the Microsoft SQL Server database. Instead, recovery of the Microsoft SQL Server database is only possible via:
  • the most recent FULL backup created before the LOG backup failure (for customers using a FULL+LOG backup strategy).
  • the most recent DIFFERENTIAL backup created before the LOG backup failure (for customers using a FULL+DIFFERENTIAL backup strategy).
  • the most recent LOG backup created before the LOG backup failure (for customers using a FULL+DIFFERENTIAL+LOG backup strategy).

Therefore, loss of data is possible. The data loss exposure is limited to the period of time between the LOG backup failure and the next FULL (or DIFFERENTIAL) backup for the affected Microsoft SQL Server database(s).

To date, this problem has only been observed with the following two scenarios:
  1. The TSM Server is out of storage space or the backup destination device fails during the LOG backup process.
  2. The connection between Data Protection for Microsoft SQL Server, or FlashCopy Manager for Microsoft SQL Server, and the TSM Server is lost during the streaming of log data to TSM



Conditions Required for Problem to Occur

The Microsoft SQL Server's transaction log is erroneously truncated only when all of the following conditions are true:
  • the SQL database is using the full or bulk-logged recovery models
  • the backup method was Legacy Backup.
      Note: The VSS backup method is not applicable to LOG backups.
  • the backup type is a LOG backup
  • the /truncate=yes option is used during the LOG backup
      Note: The "yes" value for the /truncate option is the default
  • the LOG backup fails
  • the failure occurs within a small window of time after the commitment process for streamed backup data has started and before the data has been committed on the Tivoli Storage Manager (TSM) Server

and data loss will occur only when:
  • all of the above conditions are true, and
  • the Microsoft SQL Server's database is lost before either:
  • the next FULL database backup is performed (for customers using a FULL+LOG backup strategy)
  • the next DIFFERENTIAL database backup is performed (for customers using a FULL+DIFFERENTIAL+LOG backup strategy)



How Do I Know if I am Affected?

Customers using Data Protection for Microsoft SQL Server 5.5.5.0, and earlier levels, or FlashCopy Manager for Microsoft SQL Server 2.1.x and 2.2.x, will know that the LOG backup failed via the standard backup processing messages. However, they will not know that the Microsoft SQL Server's transaction log has been truncated in error and therefore can not be used for recovery in the event of an SQL server crash or failure. Also, the failed LOG backup, plus any future LOG backups prior to the next FULL (or DIFFERENTIAL) backup, can not be used to restore the Microsoft SQL Server database.

Until a fix is available, it is recommend that you upgrade to either:
  • Data Protection for Microsoft SQL Server 5.5.5.1, later 5.5.x.x levels, or 6.3.0.x
  • FlashCopy Manager for Microsoft SQL Server 3.1.0.x
where a new warning message is available and will be presented when this problem occurs.
Note: These levels do not fix the problem. They only provide the new error message described below.


Customers using Data Protection for Microsoft SQL Server 5.5.5.1, later 5.5.x.x levels, and 6.3.x.x levels, or FlashCopy Manager for Microsoft SQL Server 3.1.x.x levels, will know that the LOG backup failed via the standard backup processing messages. They will also know that the Microsoft SQL Server's transaction log may have been truncated in error via a new message that is displayed with LOG backup failures where the conditions for this problem have been met during the LOG backup.

ACO5198W WARNING: LOG backup of <db-name> database failed but possibly log file(s) were truncated.
Do a FULL backup of the database.

However, with both of the above customer groups, data loss will only occur if the Microsoft SQL Server's database is lost during the period of time between the LOG backup failure and the next FULL (or DIFFERENTIAL) backup for the affected Microsoft SQL Server database(s).


Workaround:

1) The following workaround will prevent the problem from occurring in the first place.

Use the /truncate=no option with your LOG backups.

When this option is used, the Microsoft SQL Server's transaction log will not be truncated via LOG backup processing. The effect of this is that the Microsoft SQL Server's transaction log could be larger than usual when comparing the transaction log size with, and without, using the /truncate=no option with your LOG backups.

We understand that this may not be a desirable workaround for some customer environments.


2) The following workaround will eliminate the data loss exposure after a LOG backup failure has occurred under these conditions:
    a) For customers using Data Protection for Microsoft SQL Server 5.5.5.0, and earlier levels, or FlashCopy Manager for Microsoft SQL Server 2.1.x or 2.2.x, immediately perform a FULL backup (or DIFFERENTIAL backup depending on your backup strategy) after any LOG backup failure has occurred. Until a fix is available, it is also recommended that you update to either:
    • Data Protection for Microsoft SQL Server 5.5.5.1, later 5.5.x.x levels, or 6.3.0.x
    • FlashCopy Manager for Microsoft SQL Server 3.1.0.x
    so that you will know specifically when this problem may have actually occurred and can limit the FULL (or DIFFERENTIAL) backup to only the affected databases.


    b) For customers using Data Protection for Microsoft SQL Server 5.5.5.1, later 5.5.x.x levels, and 6.3.x.x levels, or FlashCopy Manager for Microsoft SQL Server 3.1.0.0 and later levels, immediately perform a FULL backup (or DIFFERENTIAL backup depending on your backup strategy) anytime that a LOG backup failure has occurred and the following message is shown:

    ACO5198W WARNING: LOG backup of <db-name> database failed but possibly log file(s) were truncated.
    Do FULL backup of the database
      Note: If you see this message, perform a FULL backup (or DIFFERENTIAL backup depending on your backup strategy) only for the database(s) indicated in the message. You do not need to run FULL (or DIFFERNTIAL) backup for all databases.

    Once the FULL backup (or DIFFERENTIAL backup depending on your backup strategy) of the affected database(s) is completed, there is no longer a data loss exposure due to this problem.



Affected Product, Components, Releases and Fixing Levels

The problem is seen when using either:
  • Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server
  • Tivoli Storage FlashCopy Manager: FlashCopy Manager for Microsoft SQL Server
to backup a Microsoft SQL Server's transaction log.

The changes required to alleviate this design limitation are significant and will require extensive testing. The projected availability dates for the " First fixing level" are reflective of changes and testing required.


Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server

Release
Affected levels
First fixing level
(click for download page)
5.2

Windows x86
Windows IA64
5.2.0.0 - 5.2.1.3
None

The 5.2 release of TSM for Databases went End of Support (EOS) on April 30, 2007. However, the Data Protection for Microsoft SQL Server 5.2.1.x component specifically was supported under the Tivoli Storage Manager for Databases 5.4 release cycle which went EOS April 30, 2012.

To obtain a fix for this problem, you must update to a supported release level that contains the fix - once available.

Until a fix is available on a supported release, it is recommend that you upgrade to either the 5.5.5.1 level or a 6.3.0.x level where a new warning message is available and will be presented when this problem occurs.
    Note: These levels do not fix the problem - only provide the new error message described in the "How Do I Know if I am Affected?" section of this flash.
5.3

Windows x64
5.3.0.0 - 5.3.3.1
None

The 5.3 release of TSM for Databases went End of Support (EOS) on April 30, 2008. However, the Data Protection for Microsoft SQL Server 5.3.3.x component specifically was supported under the Tivoli Storage Manager for Databases 5.4 release cycle which went EOS April 30, 2012.

To obtain a fix for this problem, you must update to a supported release level that contains the fix - once available.

Until a fix is available on a supported release, it is recommend that you upgrade to either the 5.5.5.1 level or a 6.3.0.x level where a new warning message is available and will be presented when this problem occurs.
    Note: These levels do not fix the problem - only provide the new error message described in the "How Do I Know if I am Affected?" section of this flash.
5.4

All platforms
5.4.0.0 - 5.4.x.x
None

The 5.4 release of TSM for Databases went End of Support (EOS) on April 30, 2012.

To obtain a fix for this problem, you must update to a supported release level that contains the fix - once available.

Until a fix is available on a supported release, it is recommend that you upgrade to either the 5.5.5.1 level or a 6.3.0.x level where a new warning message is available and will be presented when this problem occurs.
    Note: These levels do not fix the problem - only provide the new error message described in the "How Do I Know if I am Affected?" section of this flash.
5.5

All platforms
5.5.0.0 - 5.5.5.2
5.5.6

Note: This Fix Pack is projected to be available September 2012. A link to the fix will be provided here once available. This target availability date is subject to change at the discretion of IBM.

Until a fix is available on this release, it is recommend that you upgrade to 5.5.5.1 or later level where a new warning message is available and will be presented when this problem occurs.
    Note: The 5.5.5.1 and later levels do not fix the problem - only provide the new error message described in the "How Do I Know if I am Affected?" section of this flash.
6.3

All platforms
6.3.0.0 - 6.3.1.0
6.3.1

Note: This Fix Pack is projected to be available December 2012. A link to the fix will be provided here once available. This target availability date is subject to change at the discretion of IBM.

Until a fix is available on this release, it is recommend that you upgrade to a 6.3.0.x level where a new warning message is available and will be presented when this problem occurs.
    Note: The 6.3.0.x levels do not fix the problem - only provide the new error message described in the "How Do I Know if I am Affected?" section of this flash.

Note: Publically available fix levels (e.g. interim fixes and fix packs) are cumulative within a release.



Tivoli Storage FlashCopy Manager: FlashCopy Manager for Microsoft SQL Server

Release
Affected levels
First fixing level
(click for download page)
2.1
2.1.0.0 - 2.1.0.3

The Data Protection for SQL component provides equivalent functionality to the FlashCopy Manager for SQL component when used in FlashCopy Manager environments.

Therefore, please download, install, and use the Data Protection for SQL 5.5.6 Fix Pack (see above table), when available, to resolve this issue in FlashCopy Manager 2.x environments.
2.2
2.2.0.0 - 2.2.1.1

The Data Protection for SQL component provides equivalent functionality to the FlashCopy Manager for SQL component when used in FlashCopy Manager environments.

Therefore, please download, install, and use the Data Protection for SQL 5.5.6 Fix Pack (see above table), when available, to resolve this issue in FlashCopy Manager 2.x environments.
3.1
3.1.0.0 - 3.1.0.x

Note: This Fix Pack is projected to be available December 19, 2012. A link to the fix will be provided here once available.

Note: Publically available fix levels (e.g. interim fixes and fix packs) are cumulative within a release.



Further Assistance & Related APARs

After reviewing this document, if you think that you may be affected and have questions, or if you are not sure whether you may be affected, contact IBM technical support for further assistance. Please reference one, or both, of the following APARs:

IC70324 SQL SERVER LOGS MAY BE TRUNCATED EVEN THOUGH THE DATA PROTECTION SQL BACKUP FAILED
IC77404 ( IC70324 ) SQL SERVER LOGS MAY BE TRUNCATED EVEN THOUGH THE DATA PROTECTION SQL BACKUP FAILED.

IBM technical support will work with you to:
  • Help you determine whether your data might be affected.
  • Help you locate the appropriate fix level for your product and application environment.

Cross reference information
Segment Product Component Platform Version Edition
Storage Management Tivoli Storage Manager for Databases Data Protection for MS SQL Windows 5.2.1, 5.3, 5.3.3, 5.4, 5.5, 5.5.1, 5.5.2, 5.5.4, 5.5.5, 6.3

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Tivoli Storage FlashCopy Manager
FlashCopy Manager for Microsoft SQL Server

Software version:

2.1, 2.2, 2.2.1, 3.1

Operating system(s):

Windows

Reference #:

1599935

Modified date:

2012-12-19

Translate my page

Machine Translation

Content navigation