Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
BUG: sp_change_secondary_role Fails with Error 3101 if There Are Outstanding Transaction Log Backups
Article ID: 294397 - View products that this article applies to.
This article was previously published under Q294397
BUG #: 352508 (SHILOH)
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:
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:
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:
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.
REFERENCESSQL Server Books Online; topic: "How to set up and perform a log shipping role change (Transact-SQL)"