Backup job & Integrity checks are failing

Question

Backup job & Integrity checks are failing

ramana3327

SSCoach

Points: 19722
More actions
September 27, 2015 at 9:08 am

#311909

Hi,
I am using sql server 2012 with HADR (Always on with sql cluster).
We have database maintenance plans through wizard for full backup & DBCC CHECK DB. It was running successfully but it failed with the below error
Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection.
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)).
I can able to take the backup from query window. It is succesful. The Sql Agent has full permissions. I don't think there are any recent changes happen.
Can please advice me how to trouble shoot this issue.

Viewing 11 posts - 1 through 10 (of 10 total)

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

ramana3327 SSCoach Points: 19722 More actions · Answer 1

There is another job scheduled to run the full backup of one db to the network path. It is successful but the backup & Check db jobs which were created from maintenance plans are failing

Thanks in advance

ARC211 Ten Centuries Points: 1283 More actions · Answer 2

have you verified the connection string under job properties?

WilburBud SSCrazy Points: 2914 More actions · Answer 3

jobs which were created from maintenance plans are failing

Is it running on the active replica? Have you tried re-creating the maintenance plan ?

ARC211 Ten Centuries Points: 1283 More actions · Answer 4

That was going to be my next suggestion:

Rebuild the Maintenance Plan

I'm still currious if the connection string has the correct information

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

ramana3327 (9/27/2015)
There is another job scheduled to run the full backup of one db to the network path. It is successful but the backup & Check db jobs which were created from maintenance plans are failing
Thanks in advance

I've seen plans lose there minds like this before. Open the plan, make a minor change, save the plan, remove the minor change, save the plan again.

As a bit of a sidebar, I'd stop using maintenance plans for such things. There's lot's of decent code on the Internet to do such things but I'd recommend you write your own... best way to learn.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramana3327 SSCoach Points: 19722 More actions · Answer 6

I am able to create new maintenance plan. But the same error when tried to run that

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

ramana3327 (9/29/2015)
I am able to create new maintenance plan. But the same error when tried to run that

Based on the given error and as someone suggested previously, who are the maintenance plans trying to run as?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

Jeff Moden (9/28/2015)
As a bit of a sidebar, I'd stop using maintenance plans for such things. There's lot's of decent code on the Internet to do such things but I'd recommend you write your own... best way to learn.

I need to echo Jeff's statement here. I don't like maintenance plans at all. They're a brute-force approach and do things that don't need to be done, like rebuilding indexes that don't need rebuilding. If an error is encountered, they fail and leave the rest of the work undone.

If you have to use a canned solution, there are plenty of them out there, but Jeff is so very right. If you write your own, you'll learn a lot and fully understand how it works. You'll also be able to support it.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 9

I've written my own (started back in the last century when open source code was not readily available for such things ...) but the BACKUP aspect of it was a significant worry for me in case my code failed and I didn't realise / get a warning message, as that could put the company's key data at risk.

Because of that risk I'm not sure that I would want to write a Backup Routine from scratch any more ... other types of maintenance, such as Index Rebuild and MSDB Housekeeping cleardown probably less critical as they would fail safe - but even then a CHECKDB failing to run and leaving a corrupted database unnoticed for too long would be a bad outcome ...

ramana3327 SSCoach Points: 19722 More actions · Answer 10

I am trying to figure it out the route cause for the failure.

I created and ran the new maintenance plan but the same issue

I ran the job using sa account

I created the new maintenance plan using the sql agent account but still the same issue

There is no complaints from users. The apps are connecting but why maintenance plan only failing?