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.