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