July 3, 2024 at 3:02 am
After CU 27 update in sql 2019 backups are failing and all mainnaytainence jobs are also faling . we did not find any error on sql logs , pls assist on ho wto resolve this
Message
Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 15.0.4375.4 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 12:00:00 AM Could not load package "Maintenance Plans\Backup" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection). The SQL statement that was issued has failed. Source: Started: 12:00:00 AM Finished: 12:00:00 AM Elapsed: 0.14 seconds. The package could not be loaded. The step failed.
July 3, 2024 at 3:38 pm
It could be a bug. I doubt if many people use maintenance plans. You may want to look at Ola Hallengren's Solution.
July 3, 2024 at 7:57 pm
Weird question but did you upgrade from a previous version of SQL (2017 or older) to 2019? If so, your maintenance plan MAY be in a previous version format that broke in CU 27. To fix it, you just have to open the maintenance plan, change something non-impactful (add a comment for example) and re-save it. You can remove the change you did after you verify it works.
Make 110% sure your SSMS version matches your SQL version though. Use SSMS 2019 to make or edit maintenance plans in SQL Server 2019. Maintenance plans are SSIS in the back end (at least were in older versions of SQL Server if I remember right) and SSIS changes can blow up if you use the wrong SSMS version when editing them. I have run into all sorts of odd issues with that.
Also check that your maintenance plan is connecting to "local connection" (I think that is what it is called by default... been a while since I set up an MP). I vaguely remember having some issues with one server of mine and I had to make a special connection back to the instance instead of "local connection".
It does sound like a bug, but I think this is a good workaround with minimal work.
Alternately, you can use Ola's scripts, but my preference is not to use them. Not that they are not good, but they are "catch all" scripts. They contain stuff for multiple backup solutions (native, RedGate, Idera, etc.) and I don't need all of that "fluff" on my system if/when a bug pops up in that script. I prefer to roll my own backup scripts as I can make them short and to the point. I have 1 stored procedure for backups, one for index maintenance, one for statistics updates, and one "master" stored procedure that calls the other 3. Quick and easy to test and allows me to debug things quickly too. If a backup fails, the index/statistics stuff doesn't run (for example). Or if index maintenance fails, it can notify me of what failed and why and continue to the statistics updates or not (depending on what I want to do). Plus, if bugs do pop up or I need to change things (RedGate SQL Backup gets a new feature I want to try out), I can quickly and easily modify things and test it on a test system. Ola's scripts are too big for a "quick test" of things. They are nice when they work, but if you hit a bug, it can be a challenge to debug it.
Just my 2 cents though :).
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply