February 2, 2004 at 4:31 pm
Hello all,
I have a "warm" standby server at a client that gets logs shipped to it every 20 minutes from a production server (both MS2K SP3, Standard Edition, the log shipping is a home grown set of scripts). It's been running for months with only minor blips due to network errors during the log copy step. Today whilst I was checking on an unrelated issue I noticed that the log restore step failed some time this morning. Usually I receive an email notification, but today I didn't. The error message indicated that the restore could not occur because the standby server was in the process of restoring a log already. The standby's server configuration and security settings have not changed for over a year.
I decided to reset the whole thing, so ran sp_resetstatus, restarted SQL, tried again, same error. Saw a previous forum posting suggesting that I detach the db and reattach it. When trying to detach I received a message saying that the db couldn't be detached because it couldn't be shut down cleanly. There were no connections open. So I figured I'd just delete the db and re-establish everything. The delete operation ran for over an hour, then I got cranky and restarted the service and rebooted the server (maybe not the smartest thing to do, but...). The delete appeared to have happened after the restart. I recreated the database, then did a full backup of my production db restored it over my newly created standby db. This went fine.
So after all this hoo-hah, I ran my log shipping job and lo and behold, again with the "can't restore database log cuz it's n the process of restoring already" job. I have a suspicion that a system table somewhere has been corrupted or has an invalid value that's got the database in a transient state, but don't know what or where to look.
Any ideas out there? Any help would be greatly appreciated.
Vik
February 5, 2004 at 8:00 am
This was removed by the editor as SPAM
February 8, 2004 at 7:46 pm
Though no one has responded, here's an update:
I manually cleared the status column in sysdatabases for the database in question, then did a full backup & restore from my production server during some downtime. That worked fine, but now when I try to run my log ship job, it kaks because the database shows as "Loading", with the status column set to loading again.
Right back where I started from. At this point I think I'm going to hafta tell my client the warm standby server needs to be flattened & rebuilt, cuz I don't know what else to do.
ANY input would be appreciated at this point.
Vik
February 10, 2004 at 7:39 am
For anyone who cares, I solved my problem, here's the skinny: before I figured out the mode & status columns in sysdatabases I zapped the problem database and recreated it. Then, I did a full backup & restore from my production db, which seemed to work fine. The first log restore, however, marked the db as permanently in "loading" status.
After several iterations of the above whilst examining the backup & restore scripts, I finally figured out that when I recreated the standby db, I inadvertently assigned a different physical file name for the data file than that of the production db. My full db restore job has a MOVE clause, but since the log file name now didn't match, the log restore would barf with a "can't restore because a restore is in progress" msg.
What clued me in was that an undo file wasn't being create during the backups.
Anyhow, there you go.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply