Microsoft KB Archive/294397

= BUG: sp_change_secondary_role Fails with Error 3101 if There Are Outstanding Transaction Log Backups =

Article ID: 294397

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Developer Edition

-



This article was previously published under Q294397



BUG #: 352508 (SHILOH)



SYMPTOMS
Executing the sp_change_secondary_role stored procedure on the standby server of a log shipping pair fails with a 3101 error message if both of the following conditions are true:


 * A value of 1 is passed for the @terminate argument. Note that this is also the default value for this parameter.
 * There is at least one outstanding transaction log to be applied on the standby server.

If you run the RESTORE LOG job on the standby server by using the xp_sqlmaint extended procedure at this point, the output is:

Source database - test

Destination database - test

[Microsoft SQL-DMO (ODBC SQLState: 42000)]

Error 3101: [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.

[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

Loaded 0 files

NULL

Finished load for plan SERVER1\INST1.test_logshipping

Server: Msg 22029, Level 16, State 1, Line 0

sqlmaint.exe failed.



CAUSE
If log shipping is configured between two servers, and you need to bring the secondary or standby server online, you can run the sp_change_secondary_role stored procedure on the standby server to bring the secondary or standby server online. If you execute the sp_change_secondary_role stored procedure with the default parameters, the stored procedure performs these tasks:


 * Sets the standby database to single-user mode.
 * Copies any extra files that may need to be copied.
 * Disables the copy job on the standby server.
 * Restores any outstanding transaction logs.
 * Brings the database online on the secondary server.
 * Performs some housekeeping on the log shipping plan tables in the msdb database because that information now needs to be updated.

If the sp_change_secondary_role stored procedure is passed a value of @terminate=1 (which is also the default input value for the @terminate parameter), item 1 in the preceding sequence uses an ALTER DATABASE dbname SET SINGLE_USER command to change the state of the database and ensure that no users are connected while the RESTORE operation is attempted. However, the ALTER DATABASE statement causes it to take a shared database lock that stays in effect until the connection terminates. Items 2 and 4 are performed by using the xp_sqlmaint extended procedure, which opens a separate connection to the server, so these steps use a server process id (spid) that is different from the spid of the original sp_change_secondary_role connection. As a result, the shared database lock that is held by the stored procedure spid blocks the RESTORE LOG task that xp_sqlmaint tries to complete. Hence, the sp_change_secondary_role procedure fails with a 3101 error message.



WORKAROUND
To avoid this error, use one of the following workarounds:
 * Ensure that there are no outstanding transaction logs before you execute the sp_change_secondary_role stored procedure. Manually running the RESTORE job on the secondary server before you run sp_change_secondary_role ensures that there are no outstanding transaction logs.

-or-
 * You could pass the value of zero to the @terminate parameter (@terminate = 0) while you execute the sp_change_secondary_role stored procedure. However, the user must verify that there are no users connected to the database before the stored procedure is executed to ensure that the RESTORE LOG works successfully.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
sp_change_secondary_role is a system stored procedure that you run on the standby server when you want to bring the standby server online.