September 10, 2010 at 7:29 am
Hi All
I need some help here, I have looked and googled to no avail. I have an installation of SQL Server 2008 installed on a windows server 2008 active/passive cluster. SQL Server runs at any point on the active node until a failover if required.
Now I am getting the following errors being logged in the sqlagent.out file. I am stumped as to the repeated Could not open a connection to SQL Server [2]. [SQLSTATE 08001] Error.
2010-09-10 10:03:43 - ? [100] Microsoft SQLServerAgent version 10.0.2531.0 ((Unknown) unicode retail build) : Process ID 8800
2010-09-10 10:03:43 - ? [101] SQL Server ******* version 10.00.2531 (0 connection limit)
2010-09-10 10:03:43 - ? [102] SQL Server ODBC driver version 10.00.2531
2010-09-10 10:03:43 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2010-09-10 10:03:43 - ? [310] 16 processor(s) and 98295 MB RAM detected
2010-09-10 10:03:43 - ? [339] Local computer is ****** running Windows NT 6.1 (7600)
2010-09-10 10:03:44 - ? [432] There are 12 subsystems in the subsystems cache
2010-09-10 10:03:44 - ! [125] Subsystem 'PowerShell' could not be loaded (reason: 3)
2010-09-10 10:03:44 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2010-09-10 10:03:44 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2010-09-10 10:03:47 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2010-09-10 10:03:47 - + [408] SQL Server MSSQLSERVER is clustered - AutoRestart has been disabled
2010-09-10 10:05:04 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:07:04 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:08:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:10:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:10:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:10:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:10:03 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:10:10 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:10:10 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:15:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:15:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:15:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:20:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:20:02 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
2010-09-10 10:20:03 - ! [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
Any ideas on how I can get rid of this error would be most appreciated. Thanks guys
Regards
Matt
September 10, 2010 at 8:21 am
Open up SQL Server Configuration Manager, check Network Configuration, and make sure Named Pipes is enabled for that instance.
September 10, 2010 at 8:27 am
Named pipes is enabled, the Service account used by SQL Agent service has full rights on the database and it's default database is online.
I have seen a similar post to my question, but in this case the agent service was not running, mine runs fine, but I can't see why this error keeps coming up.
September 10, 2010 at 9:04 am
I noticed it's throwing the error every 5 minutes or so.
Is there a job scheduled to run every 5 mins that is connecting to a linked server/etc? Log shipping/replication check job or anything?
September 10, 2010 at 11:51 am
I second Derrick. Think there is a job which is trying to make the connection to SQL Server
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
June 20, 2011 at 12:17 pm
I have similar issue with SQL server 2008 R2 cluster running on windows server 2008 r2 (64 bit ).
I get about 20 -30 error message a day but I haven't been able to tie them to any of the SQL jobs. All SQL jobs are running successfully and the timing of the error message doesn't consistently matches with any of the job's schedule. I even tried using Alias (same as the SQL server Cluster name) but with no luck.
[165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]
Any help will be appreciated.
thanks,
BK
June 20, 2011 at 12:36 pm
June 20, 2011 at 2:38 pm
Yeah, I read the article earlier and I think the article talks about the error slightly different than what I have in my hands. Not like the article describes, I do not get any error when trying to get connected to the server from workstation/servers/application. The error message only appears in Error Logs for the SQL Server Agent. Interestingly, even the error messages are logged; none of my SQL jobs are failing.
Thanks,
BK
November 15, 2011 at 2:05 am
It's the same situation to me, sql server 2008 r2 , win2k8 r2
there is no error with job. every jobs running succesfully.
I did various action, create alias, hostfile, agent job connection propery, regedit, etc...
but I have no idea.
I hope resolve this issue quicly ~~
June 10, 2013 at 12:43 pm
I found a fix for this happening on a 64 bit cluster with Windows Server 2008 running SQL Server 2008r2. In the Failover Cluster Manager on the left nav click on SQL Server under Services and applications then in the summary click on SQL Server Agent and right click and click bring this resource online. That fixed my issue of SQL Agent running but yet there were errors in the log [[165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001]]. I hope this helps some others save some time!
June 2, 2014 at 7:07 am
Hi,
Sorry to re-open old posts.
Did anybody have a successful resolution to this? I have a installation of SQL 2008 R2 on Windows Server 2008 R2 in an clusterd solution.
SQL Server and the SQL Agent services are successfully running and have been doing so for some time.
However, i am getting the error in the SQL Server Agent error logs; [165] ODBC Error: 0, Named Pipes Provider: Could not open a connection to SQL Server [2]. [SQLSTATE 08001].
Named pipes is enabled for the instance.
The service account used by SQL Agent has full rights and the default DB is online.
All the jobs run successfully.
Any help would be greatly appreciated.
Thanks,
Dave
June 11, 2014 at 3:36 am
I've now found the source of this error; for anybody who is suffering from the same problem, hopefully this might help;
The error was due to the scheduled jobs that were running on the instance. Even though they never failed and were configured to quit the job reporting a failure, this error was still appearing in the SQL Agent Error Log. I had to disable all of the jobs and then re-enable one at a time to find the culprit.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply