Microsoft KB Archive/308774

= FIX: Sp_change_primary_role May Store Transaction Log Backup in the Wrong Location =

Article ID: 308774

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q308774



BUG #: 352343 (SHILOH_BUGS)



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



WORKAROUND
In the log shipping setup for a named instance, do not select the default paths for the folder as the storage location for the transaction log backups. Specify any folder other than the default.



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.