Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stand by/read only database on SUSPECT Expand / Collapse
Author
Message
Posted Monday, December 10, 2012 12:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:35 PM
Points: 9, Visits: 125
Hi all, I had the issue twice back to June, and Oct this year.
Our environment uses log shipping between two long distance geographical locations. One day on June(another on Oct), the secondary db restore job was failed, the secondary db was on SUSPECT mode.

As searched from forum, tried to set the db to emergency mode, but error message indicates the action failed because the db was still in restore process.

Since the secondary db is designed as stand by read only mode, am I right that the db couldn't be backup? So that on secondary db location there won't be any full backup set to recovery.

What had i done was:
1. stop log shipping from primary db;
2. started a new full backup on primary db;
3. build a maintenance plan for primary db log backup;
4. copy the full backup and backup logs from primary db location to secondary db location(where i work);
5. restore full backup and the backup logs in stand by mode;
6. restart a new log shipping job on primary db.

My question: is this would be best way to deal with the issue, is there any other way could be resolving the issue faster, my primary db is 120GB, copy full backup(even zipped) over through network will take 15+ hours, and my secondary db need to be accessible according to our business requirement.

I hope someone could answer my question.

thanks

Post #1394738
Posted Tuesday, December 11, 2012 4:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
yi.hua72 (12/10/2012)
Hi all, I had the issue twice back to June, and Oct this year.
Our environment uses log shipping between two long distance geographical locations. One day on June(another on Oct), the secondary db restore job was failed, the secondary db was on SUSPECT mode.

As searched from forum, tried to set the db to emergency mode, but error message indicates the action failed because the db was still in restore process.

I have never heard of a secondary DB going from Standby/Readonly into Suspect mode. That is an interesting scenario. You would need to recover the database before you could try changing to the Emergency state and if it is Suspect I am not sure you could even accomplish that. Did you try? Even if you could, recovering the database would break log shipping so it would be of no help to you.

Since the secondary db is designed as stand by read only mode, am I right that the db couldn't be backup?

Correct. You cannot backup databases in Standby/Readonly.

So that on secondary db location there won't be any full backup set to recovery.

What had i done was:
1. stop log shipping from primary db;
2. started a new full backup on primary db;
3. build a maintenance plan for primary db log backup;
4. copy the full backup and backup logs from primary db location to secondary db location(where i work);
5. restore full backup and the backup logs in stand by mode;
6. restart a new log shipping job on primary db.

My question: is this would be best way to deal with the issue, is there any other way could be resolving the issue faster, my primary db is 120GB, copy full backup(even zipped) over through network will take 15+ hours, and my secondary db need to be accessible according to our business requirement.

Given what you have said I think rebuilding log shipping from a new backup of the primary database is your only option and the steps you outlined look fine to me.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1395353
Posted Tuesday, December 11, 2012 4:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 7,097, Visits: 12,599
PS Did you ever figure out why the database went to Suspect? Was there anything of interest in the SQL Server Error Log?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1395357
Posted Wednesday, December 12, 2012 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:35 PM
Points: 9, Visits: 125
thanks a lot! opc.three,

the very last message on sql log for that database is "The database <db_name> is marked RESTORING and is in a state that does not allow recovery to be run."

I checked the error log, didn't found any information related the time when the "suspect" happening.

even more went into physical sql box to verify the event log, had nothing found neither.

My guess it could be some hardware issues(not failure)

If the issue would have stroke back, i will post all log info, see if i would missed something on previous verification.

Thanks again for your reply.
Post #1395808
Posted Thursday, April 18, 2013 1:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:09 PM
Points: 549, Visits: 1,181
HI
even i have faced this complicated issues after dc dr drill
[switch over and switch back ] secondary database is corrupted and gone on into suspect mode uanble to recover
steps i followed

1. primary database size is 100gb nearly so it is difficult to copy and restore [or ] by using file transfer from unix box etc... the backup file from primary to secondary it will take so much of time and users might effected the downtime of business

2. created a device backup in primary server and divided the backup file in to 10 parts of 10Gb [total 100gb and more ]
3.created a maintenance plan job for present and future use also and executed the device backup maintenance plan.. after completing your backup disable the JOB

3. collect all divided backup files and try to copy one by one from primary to secondary location which might not cause network break for transferring the file [if file size is large it might cause network link fail ]

4. delete old database and restore the backup files in the secondary with stand-by/read-only mode and try to synchronize the logshipping database


Thanks & Regards
NAGA.ROHITKUMAR
Post #1443643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse