May 10, 2017 at 1:24 pm
I get the following error when a job executes our maintenance plan: Description: The Execute method on the task returned error code 0x80131501 (Failed to connect to server (local).). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2017-05-10 14:52:18.46
I've tried to find the meaning of the error code 0x80131501 but haven't found it yet. I tried here https://docs.microsoft.com/en-us/sql/integration-services/integration-services-error-and-message-reference but that error code is not listed. Is there a better list that includes the error code I need?
I've tried searching for people with similar problems however those I have found were after someone installed the first CU for SQL Server 2016 and they also indicated that using Windows NT Integrated security solved the problem. Trouble is, that is what we use for our Maintenance Plans and we are still getting this error. I tried switching to a specific user name and password which I created as a sysadmin and still got the same error.
Reviewing the file created by the maintenance plan under Reporting and Logging doesn't give any useful information even when I check off Log extended information. I've tried to view the maintenance plan log file to get more details but either didn't find it or if I did it gives no additional information on this error. Would someone mind telling me how to view the maintenance plan log file so I can be sure that I have found it?
I tried creating a new maintenance plan with on backup task in it, but when I execute it I get the same error. I tried creating a new connection in an existing maintenance plan, but still got the same error.
Can anyone help me troubleshoot this problem and find a solution?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 11, 2017 at 7:04 am
download and use Ola Hallengren's scripts and junk the maintenance plans
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 11, 2017 at 2:51 pm
I have considered scripting the t-sql from the plans and using that code as a temporary fix, however I prefer to keep using the maintenance plans.
Has anyone dealt with this problem before and found a way to get the maintnance plans working again?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 11, 2017 at 7:53 pm
Don't know why you are hitting that error - I don't use Maintenance plans myself. But I'm pretty sure that error number is just failure to connect to server as it's showing.
The maintenance plan logs are wherever you would have set them to go - the default being the SQL instance log directory.
Open the maintenance plan and look to the right of the Manage Connections. There is an icon that looks like two reports. Click that and it says where the log file goes.
The naming convention is something like: MaintenancePlanName_SubplanName_yyyymmddhhmmss.txt
I just created one and ran it to look. So it's just a guess based on that.
Sue
May 12, 2017 at 8:09 am
Thanks.
It looks like I was looking in the right place for the maintenance plan logs and they don't offer any useful information about the error I am getting.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 12, 2017 at 8:21 am
Try:
1. Check the rights of the SQL and Agent service accouts.
2. Try doing a small backup from SSMS.
3. Try creating an agent job to do a small backup and run it.
etc
ps I would also reccommend Ola Hallengren's scripts.
May 12, 2017 at 8:27 am
Robert W Marda - Friday, May 12, 2017 8:09 AMThanks.It looks like I was looking in the right place for the maintenance plan logs and they don't offer any useful information about the error I am getting.
That is not unusual with maintenance plans.
Did you specifying the server, instance name specifically for the connection instead of using local connection? One other thought - Did you rename your server recently?
Sue
May 12, 2017 at 8:44 am
I agree with Sue. It sounds like your connections are buggered up.
I had a similar issue ages back when I was doing upgrades from 2008 R2 to 2012 and my aliases got screwed up due to server migration along with the upgrade. My fix was to add a second connection using the full name (server\instance,port) for any SQL instances that complained about that "Local server connection" error. Some didn't complain, but most did.
Mind you I do agree that maintenance plans are more work than they are worth. Scripts are a lot nicer and a LOT more customizable. I still am using Maintenance plans mostly because I haven't had time to migrate to scripts. One of these days I'll change over...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 12, 2017 at 2:59 pm
I thank you all for your replies so far. As I have been going through them and getting ideas on what to look at I have been narrowing down the problem.
One that was particularly helpful is from Sue. Because you mentioned using the port I went to the SQL Server log file to get it and found login failures during the time the maintenance plans run (and I had looked at the SQL Server log before but missed this the first time I looked). I then realized that the databases sited in the login failure are offline. Most of our plans do a full backup of all databases and to avoid a failure for offline database we check off the box labeled "Ignore databases where the state is not online". I verified that these check boxes are still checked.
When I change to only selected databases the maintenance plan will succeed and when I bring offline databases online the maintenance plan will succeed.
So from what I can tell, since installing CU2 on our SQL Server 2016 servers, SQL Server is not looking at that check box and is attempting to backup offline databases even though it should not because the check box to ignore databases that are not online is checked.
Has anyone seen this before?
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 12, 2017 at 3:21 pm
I heard of that on earlier versions but not on 2016. Doesn't mean much though. I thought adding that ignore if they aren't online addressed that.
Here is one thing you may want to check - when you go to modify the databases to backup and click on the databases drop down, if you go to the selection for These Databases - do the offline databases show up in that list as well?
And if you select the all databases with the "ignore if they aren't online" option, do the offline databases show up when you Click the View T-SQL?
If they are showing up in those lists, did you also check the statuses by querying sys.databases?
Just trying to figure out where it is messed up.
Sue
May 12, 2017 at 4:13 pm
Not 100% sure on the back end magic of the maintenance plans, but I have a sneaking suspicion that that checkboxes and radio buttons only applies at the time the plan is created.
What I mean is if you go into that, uncheck the box, hit save, then check the box and hit save again, your currently offline databases will not be backed up. BUT if you turn one of them back online and do the backup, you won't get a backup of that newly online database. AND if you take a previuosly online database offline, I bet the backup will fail.
Looking at a recently created database, we have several indexes with no statistics yet we have a maintenance plan set to rebuild statistics weekly. Looks like I need to go in and poke that MP... hopefully I get time to turf those soon...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 12, 2017 at 4:37 pm
It doesn't change. I was just wondering if it initially picks it up okay. Just due to Robert having done a test by creating a new one earlier and it failed. I was tracing while messing with one and it did check sys.databases when creating or modifying it. But it won't have any knowledge of changes after the fact.
Just can't figure out why the earlier new maintenance plan had the same failure.
Sue
May 15, 2017 at 8:23 am
Any database that is offline doesn't show up in the list of databases available for backups.
When I switch a maintenance plan from all database to those I specify the maintenance plan succeeds. When I switch an offline database to online the maintenance plan succeeds and a backup for that database gets created. When I switch the database back to offline the maintenance plan fails again.
When I click the View T-SQL no backup command for the offline database shows up.
When I look at sys.databases the offline database shows as offline.
This was working fine on SQL Server 2016 up until we installed cumulative update 2.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
May 15, 2017 at 10:48 am
I installed our first SQL2016 with CU2 applied and this is what I have noticed. If you create a Maint plan with a SQL Server auth ID it doesn't keep the password. If you I use WIndows Auth it works.
May 15, 2017 at 11:52 am
At one point I thought this problem might be permission related because the error in the SQL Server log starts out with "Login failed for user..." However this error and my maintenance plan failing go away when I modify the maintenance plan from taking actions (such as integrity checks and full backups) on all database to databases I specify and I check off all online databases. I made no changes to permissions nor the account used to execute the maintenance plan.
Although switching to selected databases resolves the problem it is a work around as I need to use the all databases option for when someone creates a database and doesn't tell me.
If you have time Markus, create a test database, switch it to offline mode, make sure your maintenance plan is set to backup all databases (and not specific databases), and check off the check box for Ignore databases where the state is not online and see if your maintenance plan fails.
I finally found someone else who has the same problem I have. It was posted as a bug on Microsoft Connect. Unfortunately there are no responses as to a fix.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply