August 14, 2006 at 6:22 am
Hi,
I have a setup two instance of SQL Server 2000 Enterprise edition on a Windows 2003 server, and have setup Log Shipping for a database from the default to the named instance.
I have selected the option "Allow Database to assume Primary role". The Log Shipping goes fine till i try to perform the failover.
The "sp_change_primary_role" on the Primary server works perfectly. When i try to run the the "sp_change_secondary_role" on the secondary server, the SP fails. When i check the Log Shipping Monitor for error details, the error reported says "the DB is in use and exclusive access couldn't be obtained." When i run the "sp_who2", i do not see any connections to this database at all. Can some please advice?
Thanks
Ravi
August 14, 2006 at 7:19 am
Could it be possible that the log shipping restore job is attempting to restore a log at that point in time?
Just because you cannot see a connection the db doesn't mean there isn't one. If someone is running a fully-qualified query against the database whilst they are connected to another database it won't show up in sp_who2.
Either way, you'll either have to find the connection and kill it or restart the sql services. If all else fails and you've successfully applied all T-Logs to the secondary you could run:
restore database <database name> with recovery
to bring it online. I'm not sure what effect that would have on subsequently running sp_change_secondary_role though.
August 14, 2006 at 7:54 am
Hi Karl,
I have the log restore job disabled, so i think i can rule that out.
And also this setup is on my test(Lab) machine, so, i am the only user connected to the SQL Server. Any other causes??
Thanks
Ravi
August 14, 2006 at 8:00 am
Ravi,
given the error message it is unlikely that there is another cause. In other words, chances are that there is a connection to the database - whether it's your user or a system process.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply