February 16, 2009 at 8:29 am
Do not ask why, there are reasons for this.
We use the following swapping mechanism to swap databases so that users RS data is refreshed from an ODBC source.
USE master
/* Step 1. Close User access and terminates o/s transcations.*/
ALTER DATABASE Livedb
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
/* Step 2.This will become the Livedb_Update later*/
ALTER DATABASE Livedb MODIFY NAME = Livedb_temp;
/* Step 3.This has the new data from IngresServer */
ALTER DATABASE Livedb_update
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE ;
/* Step 4.This puts the Updated data Live */
ALTER DATABASE Livedb_update MODIFY NAME = Livedb;
/* Step 5.Reinstates user acceess */
ALTER DATABASE Livedb
SET MULTI_USER;
/* Step 6.Previous Livedb becomes Update */
ALTER DATABASE Livedb_temp MODIFY NAME = Livedb_update;
/* Step 7. Ready for new data from IngresServer */
ALTER DATABASE Livedb_update
SET MULTI_USER;
This had been working fine until recently. It now occasionally stalls after step 3 leaving the databases in Restricted User status and no Livedb for RS to use.
Any suggestions as we have hit a brick wall on this one.
February 16, 2009 at 8:58 am
Are there any error messages in the error log?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 16, 2009 at 9:34 am
Jack
The job is called as a batch job in Server Agent. No messages in the error log. In fact the job completes successfully. There does not appear to be any locks using sp_lock nor any process sp_who2. We thought we had it licked when we put a 1 second WAIT.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy