Sometimes on a heavy loaded server(many databases/connection). You may get an error log when your backup maintenance plan job gets fails with the following error
Message:
Executed as user: xxx\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:00:25 AM Could not load package “Maintenance Plans\xxx” because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0×80004005 (Unable to complete login process due to delay in opening server connection). The SQL statement that was issued has failed. Source: Started: 12:00:25 AM Finished: 12:01:14 AM Elapsed: 49.765 seconds. The package could not be loaded. The step failed.
Which indicates that your maintenance plan which interns uses ssis (integrated services) and msdb databases is delay in connection. To resolve this issue general recommendation could be
- Change the host file
Which is generally located at “C:\WINDOWS\system32\drivers\etc\host” add server name and an ip address.
This could solve your problem.
- Change the “Delay” setting in package property , package property ->Control flow delay.
And the third one which generally would be the best alternative and will mostly solve this issue is
- Change the number of retry for the specific job to greater number (eg. 5 or more) and also can change the retry interval to (0). This way if this job fails with delay in connection, will retry again till specified number of times and mostly it gets succeeded.
Generally we have observed this with environment : Cluster and x64 sql server 2008
