Microsoft small business knowledge base

Article ID: 294397 - Last Review: June 25, 2004 - Revision: 3.3

This article was previously published under Q294397
BUG #: 352508 (SHILOH)

On This Page


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
Finished load for plan SERVER1\INST1.test_logshipping

Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.


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
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.


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.

  • 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.


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


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.


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

  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
kbbug kbpending KB294397
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support