FIX: Sp_change_primary_role May Store Transaction Log Backup in the Wrong Location (308774)

SYMPTOMS

The sp_change_primary_role stored procedure may store the final transaction log backup in the \%Windir%\System32 folder instead of the folder that you specify when you set up log shipping.

On named instances of SQL Server 2000, the last transaction log may not load when you execute the sp_change_secondary_role stored procedure because the transaction log backup is not in the expected location, which may cause a loss of data.

CAUSE

As part of a log shipping role change, the sp_change_primary_role stored procedure performs a final transaction log backup. SQL Server uses the final transaction log backup file and updated .tuf file for the database for the last update of the secondary database. Both files (.trn and .tuf) should be located in the transaction log folder, which you configure when you setup log shipping.

However, if you specify all the default paths (\MSSQLServer\backup folders) when you setup log shipping, the final transaction log and the .tuf file may be stored in the \%Windir%\System32 folder instead of the default folder you specified in the initial setup.

When SQL Server executes the sp_change_secondary_role stored procedure it performs a fail over to the second server and a load of the last transaction log. On named instances of SQL Server 2000, because the transaction log and .tuf file may not be in the default folder, the last transaction log may not load during the execution of sp_change_secondary_role.

This behavior only occurs on named instances of SQL Server 2000.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

REFERENCES

SQL Server 2000 Books Online; topics: "How to configure log shipping (Enterprise Manager)"; "How to set up and perform a log shipping role change"; "Monitoring Log Shipping"