On a Clustered SQL Server 2005 running on Windows 2003 machine the maintenance plan MIGHT fail with: “The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired)”

  • Artykuł

On a Clustered SQL Server 2005 running on Windows 2003 machine the maintenance plan fails with below error. This issue is also seen on a newly created a maintenance plan as well.

Executed as user: <Domain>\User. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:24:17 PM Could not load package "Maintenance Plans\Test1" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed. Source: Started: 4:24:17 PM Finished: 4:24:33 PM Elapsed: 15.281 seconds. The package could not be loaded. The step failed.

CAUSE:

This occurs if the dependencies of the Microsoft SQL Server Cluster Resources are set incorrectly.

RESOLUTION:

We found a work around for the Maintenance Plan to execute by editing the job step and adding a ‘\’ to the package name.

However we also found that the SQL Server virtual name to which the user was connecting and the name of the instance in the SQL Server Error Log are different, which isn't normal. In the SQL server error log it was showing windows cluster name instead of SQL Server Virtual name.

In the windows Cluster administrator, we found that the resources dependencies were incorrect. We preformed the below changes to rectify the issue.

All the Cluster resources (Cluster, MSDTC and SQL Server) were in the same group in this case.

SQL Server physical disk resource is dependent on CLUSTER NAME and CLUSTER IP. Physical disk should not be depending on anything. We have removed the dependency of Windows cluster and Cluster Name from SQL server Physical Disk. After this change it was showing SQL Server virtual name in the SQL Server error log.

The above changes done to the Windows Cluster dependency helped us resolve the issue.

After this creation of new Maintenance Plan were executing successfully & we never needed to add the “\” in the package path in the job step.

Gurlen Singh Sodhi

SE, Microsoft SQL Server

Reviewed by:

Ouseph Devis T

Tech Lead, Microsoft SQL server

Comments

  • Anonymous
    June 17, 2010
    We had this exact problem.  I also notices that if you edit the SQL Agent job and reselect the package path, it'll readd the to the front of your path, which is a good indication that your cluster resource is setup incorrectly. Thanks for posting.

  • Anonymous
    July 20, 2010
    Hey Tim , thank you so much. Keep tuning in to this blog for more updates

  • Anonymous
    September 23, 2010
    Great Workaround. This saved me a massive headache.

  • Anonymous
    December 11, 2010
    I am already having same setup but today i for same error 2-3 times. Any other way.

  • Anonymous
    February 02, 2011
    Should this also affect windows 2008 r2 with sql 2008r2?  I show no dependencies for my physical discs, yet I still have to add the preceding to my maintenance plans.  Any suggestions?