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