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

Article translations Article translations
Article ID: 294397 - View products that this article applies to.
This article was previously published under Q294397
BUG #: 352508 (SHILOH)
Expand all | Collapse all

On This Page

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.

REFERENCES

SQL Server Books Online; topic: "How to set up and perform a log shipping role change (Transact-SQL)"

Properties

Article ID: 294397 - Last Review: June 25, 2004 - Revision: 3.3
APPLIES TO
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
Keywords: 
kbbug kbpending KB294397

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com