SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stand by/read only database on SUSPECT


Stand by/read only database on SUSPECT

Author
Message
YeeHwua
YeeHwua
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 274
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39406 Visits: 14411
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39406 Visits: 14411
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
YeeHwua
YeeHwua
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 274
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.
naga.rohitkumar
naga.rohitkumar
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1556 Visits: 1378
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
Naga.Rohitkumar
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search