December 27, 2011 at 7:29 am
I have a SQL 2008 SP1 that all of a sudden after working for well over a year this job started getting this message starting yesterday. I have tried to manually run it and it gets this error. I created a new Maint plan and it gets the same message. I did some searches and found this issue happened in SQL 2005 and never got a clear cut reason as to why and what the fix was. Has anyone run into this?
The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Unable to complete login process due to delay in opening server connection). The SQL statement that was issued has failed.
December 27, 2011 at 8:18 am
Was there any change done on the Network layer? This look more like somebody changing the IP or DNS entry or both. You could try extending the timeout to higher amount.
-Roy
December 27, 2011 at 8:21 am
nothing has changed as far as I know.... Why would changing the IP address cause a timeout error though? THere is no dns alias on this server. Where is the timeout setting at?
December 27, 2011 at 8:28 am
If the IP is changed and the DNS is not flushed/propogated, the client when it looks up using server name might get resolved to old IP address. This will throw this error.
-Roy
December 27, 2011 at 8:33 am
There is connect item on it (or similar). This is what Microsoft suggested on that...
There are three things to check.
First is the capacity of the server, for sql server, in ssms, right click on server node => Connections tab, and check the Remote query timeout is big enough.
2nd is the connection timeout, this can be changed through connection string, by adding somehting like ";connect timeout=50". Notice that this setting is provider-specific.
3rd is the Command timeout, most of the ssis adaptors has a CommandTimeout property that can be found in advanced editor. 0 means infinite.
Check and modify the three settings to avoid timeout.
December 27, 2011 at 8:44 am
The server typically is hardly used ever at all... memory is good, CPUs are very low all of the time.
The Remote Query timeout is currently set to 600... I bumped it to 2000 and that did not help... Didn't think it would since this is a Maintenance plan executing on this server anyway. I just checked with everyone and nothing has been changed on this server.. IP address is the same as it always has been.
December 27, 2011 at 9:24 am
By any chance was a firewall enabled on the Server? Any local policies changed? Those are the only things that come to me.
-Roy
December 27, 2011 at 10:12 am
Bizzare.... just bizzare.... the jobs are succeeding now....
Thanks everyone for your input.
January 2, 2012 at 3:11 am
Markus (12/27/2011)
Bizzare.... just bizzare.... the jobs are succeeding now....Thanks everyone for your input.
Nice to hear that the issue is resolved. 🙂
By the way, could you identify what exacly was the problem and what's the solution ?
Thanks.
January 2, 2012 at 7:22 am
Scheduled jobs that have been running for two years Integrity Check and Update stats started failing with the above listed error. I did nothing to resolve it.... a few hours after looking into the problem I ran the job and it succeeded. Weird.
January 2, 2012 at 7:32 am
Markus (1/2/2012)
Scheduled jobs that have been running for two years Integrity Check and Update stats started failing with the above listed error. I did nothing to resolve it.... a few hours after looking into the problem I ran the job and it succeeded. Weird.
Consider it your New Year gift and enjoy... 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply