• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Problem removing log shipping when secondary server or shipping server not contactable

Hi

I had log shipping implemented on a database but, due to WAN problems the primary and shipping servers can no longer contact the secondary server. However I cannot remove the log shipping maintenance plans as an attempt to do so (through EM) generates error "Unable to contact the specified server. Make sure the server is running and the connection information is correct" and then throws me out of maintenance plan.

How can I force removal of this plan?

Many thanks
0
RadicalSoftwareSolutions
Asked:
RadicalSoftwareSolutions
  • 6
  • 6
1 Solution
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Try pointing the linked server to a 'dummy' linked server available then try to remove it again...Hope this helps...
0
 
RadicalSoftwareSolutionsAuthor Commented:
I can't do that as I can't alter the log shipping plan - as soon as it opens the the log shipping tab the error appears then it throws me out
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I was thinking about modifying only what's written in the sysservers table...Could you run the following query and post the contents...

use master
go
select * from sysservers
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RadicalSoftwareSolutionsAuthor Commented:
Here's the results - can't copy the headings though - is this OK gues you would be able to work out column headings for similar query on your system

0      1089      DBServer1       SQL Server      SQLOLEDB      DBServer1       NULL      NULL      2005-05-07 14:11:41.403      NULL      NULL      NULL      NULL      0      0      DBServer1                       0      1      0      0      0      0      1      0      0      0      1      0      NULL
1      1249      DBServer2       SQL Server      SQLOLEDB      DBServer2       NULL      NULL      2006-01-12 11:34:05.310      NULL      NULL      NULL      NULL      0      0      DBServer1                       1      1      0      0      0      0      1      1      0      0      1      0      NULL
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You may consider modifying the DBServer2 line 1 so that it points out to some other server...This assumes DBServer2 is the server that can not be contacted...

WARNING: turn off service and copy the master.MDF and master.LDF first so that you can go back in case the procedure would not work...

That may work...
0
 
RadicalSoftwareSolutionsAuthor Commented:
Tried that but get error "ad hoc updates to system catalogues are not enabled"

Any ideas?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
B4 modifying the content run this first...

EXEC dbo.sp_configure 'show advanced option', '1'
GO

RECONFIGURE WITH OVERRIDE
GO

run your modification...then run this

--Show the current value
EXEC dbo.sp_configure 'show advanced option', '0'
GO

RECONFIGURE WITH OVERRIDE
GO
0
 
RadicalSoftwareSolutionsAuthor Commented:
Hi

That didn't appear to make any difference. I still got the same error when attempting to change the column value of srvname
0
 
RadicalSoftwareSolutionsAuthor Commented:
Hi

I found a setting in the server properties that allows System catalog Updates - having set that I was able to change the sysservers table and delete the log shipping maintenance plan.

many thanks for all your help

Regards

Ian
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<That didn't appear to make any difference.>>It should work..Did you run both statements separately?  Don't forget to put it back to what it was...

Now about grading...you should read this from EE gudelines below...Keep in mind that experts do have access to your access to your grading history and will be less inclined to help if they find a pattern of low grades (B and C)

<<Grading at Experts Exchange is not like school. It's more like the "10-point Must" system in professional boxing; in other words, an answer is worth an A, unless it doesn't resolve your issue. If it requires you to do a little more research, or figure out one more piece of code, then it's worth a B. If you think it's not worth a B, the custom is to offer the Experts an opportunity to earn a better grade.>>

Good luck )
0
 
RadicalSoftwareSolutionsAuthor Commented:
Hi

I did run the commands seperately i.e.

1) EXEC dbo.sp_configure 'show advanced option', '1'
GO

2) RECONFIGURE WITH OVERRIDE
GO

3) try to make change but still not able to.

That is why I awarded a grade of "Good" as opposed to "Excellent" as I had to do some research in order to find the "Allow system catalog update" setting. I though that was fair
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Good...Glad it finally worked out for you...;)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now