Feb 26, 2017 7:30:31 PM by Pinal Dave
One of my clients contacted me and informed that after patching of SQL Server, they noticed that their maintenance plan were failing. I asked them to share the complete error message about the Maintenance Plan failing with 0x80131904.
End Warning Error: 2017-01-09 17:22:27.10 Code: 0xC0024104 Source: Back Up Database
Task Description: The Execute method on the task returned error code 0x80131904
(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)).
The Execute method must succeed, and indicate the result using an “out” parameter.
I have removed some part of a message which is not relevant. In above long error information, interesting piece is “error: 26 – Error Locating Server/Instance Specified”.
Whenever we create a Maintenance Plan, SQL Server Management Studio always takes the name of the connection manager as “Local server connection” by default.
The important thing to note is, it uses the server name that we have used in SSMS to connect to the instance and puts this exact name in the server name property of the connection manager “Local server connection”
Does this give some hint?
It is quite possible that the port of SQL might have been changed. When we connected earlier, when maintenance plan was created. We need to check on what is the server name, value in the connection manager “Local server connection” mentioned. To see that we can click “Manage Connections” after we click modify the Maintenance Plan.
Now, we can use the SQL Server Configuration manager and create an alias with the exact same name, provide the TCP and manual port number. Once we created alias, maintenance plan ran without an error?
Hopefully you know how to create TCP aliases in SQL Server Configuration Manager. If not, refer to books online documentation Create or Delete a Server Alias for Use by a Client (SQL Server Configuration Manager)
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Tags: SQL Server
Written by Pinal Dave
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.