Logshipping

Question

Logshipping

vijaysingh

SSC Eights!

Points: 982
More actions
March 3, 2009 at 3:04 am

#131422

Hi All,
I had setup the logshipping for the test purpose and its running fine with out errors. I updated a row in the primary Server. Now the problem is i am unable to check the updated row in the secondary Server and unable to take the query window which is working for logshipping. I tried to run the query like " Select * from [DB_NAME].[SCHEMA_NAME].[TABLENAME]"
Its giving the below error
Msg 927, Level 14, State 2, Line 1
Database 'MANAGEMENTNEW' cannot be opened. It is in the middle of a restore.
And one more problem is LS_Alert job always fails and gives the below error
Message
The job failed. The Job was invoked by Schedule 105 (Log shipping alert job schedule.). The last step to run was step 1 (Log shipping alert job step.).
Please advise
Regards,
Vijay Singh.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

vijaysingh (3/3/2009)
Hi All,
I tried to run the query like " Select * from [DB_NAME].[SCHEMA_NAME].[TABLENAME]"
Its giving the below error
Msg 927, Level 14, State 2, Line 1
Database 'MANAGEMENTNEW' cannot be opened. It is in the middle of a restore.

How did you setup your secondary database, in RESTORE mode? If so you would not be able to query the database. Check it.

And one more problem is LS_Alert job always fails and gives the below error
Message
The job failed. The Job was invoked by Schedule 105 (Log shipping alert job schedule.). The last step to run was step 1 (Log shipping alert job step.).

Did you check the error log? Check the error log please and give us a bit more information?

vijaysingh SSC Eights! Points: 982 More actions · Answer 2

Hi,

Below is the information what i got when i checked the error log of LS_Alert Job

Date3/3/2009 4:58:00 PM

LogJob History (LSAlert_DEEPAKA\SQL2005)

Step ID0

ServerDEEPAKA\SQL2005

Job NameLSAlert_DEEPAKA\SQL2005

Step Name(Job outcome)

Duration00:00:01

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

The job failed. The Job was invoked by Schedule 105 (Log shipping alert job schedule.). The last step to run was step 1 (Log shipping alert job step.).

Regards,

Vijay Singh

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

vijaysingh (3/3/2009)
Hi,
Below is the information what i got when i checked the error log of LS_Alert Job
Date3/3/2009 4:58:00 PM
LogJob History (LSAlert_DEEPAKA\SQL2005)
Step ID0
ServerDEEPAKA\SQL2005
Job NameLSAlert_DEEPAKA\SQL2005
Step Name(Job outcome)
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
The job failed. The Job was invoked by Schedule 105 (Log shipping alert job schedule.). The last step to run was step 1 (Log shipping alert job step.).
Regards,
Vijay Singh

can you query logshipping monitor tables?

log_shipping_monitor_error_detail and log_shipping_monitor_history detail

and see whether you can find more information?

rajiniforu Ten Centuries Points: 1147 More actions · Answer 4

This typically seems like the database was not put in 'standby mode' which should have been ideally done. As such you are not able to query the database.

Can you please check on this term. Also check if the LS_restore jobs are running without any issues. Even if the restore job fails, it would put the database into 'loading state'. This will prevent any users accessing the database.

-Rajini

vijaysingh SSC Eights! Points: 982 More actions · Answer 5

Hi Krishna,

In my case i have not used monitor instance.

Hi rajiniforu,

All jobs are running good except Ls_alert Job.

As I am new to Db And Sql Server Can you guys give a breif Details to set up Logshipping.

I Need Your Help Guys.

Regards,

Vijay Singh

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

vijaysingh (3/3/2009)
Hi Krishna,
In my case i have not used monitor instance.

No, Vijay this is a system table to monitor the log shipping instance. it does not matter whether you have setup monitor instance or not.

And also as advised before, did you check whether you set up your secondary as Restore or Standby mode.

If you have set up in recovery mode, the secondary database is in recovery state and you will not be able to access it. IF you have setup in Standby, then you would be able to query the database.

check this out :

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309728,00.html

vijaysingh SSC Eights! Points: 982 More actions · Answer 7

Hi Krishna,

I am unable to execute logshipping monitor tables and my setup is in restore mode.

Ill refere the link given by you and i will get back to you.

Thanks and Regards

Vijay Singh.:)

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

vijaysingh (3/3/2009)
Hi Krishna,
I am unable to execute logshipping monitor tables and my setup is in restore mode.

You should query them on against your primary server, all the log shipping information and error details will be stored in your primary server unless you have a monitor server, it will be stored in both instances.

You can also query sysjobhistory and sysjobactivity against msdb database.