Issue Access Named Instance

Question

Issue Access Named Instance

arrjay

Hall of Fame

Points: 3455
More actions
April 1, 2014 at 3:34 am

#283961

Hi - I have recently updated my memory allocation using SQLCMD. I increased it to 4036 which should be enough memory for the instance however I am now being hit with the following error;
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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
I've seen this error on numerous occasions and generally can be fixed by enabling TCP/IP or starting the services. All are on or started.... I wonder why changing the memory allocation would do this. Any ideas?
Thanks!
--------------------------------------------
Laughing in the face of contention...

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Looks like the instance isn't running. Check that the appropriate service is running

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arrjay Hall of Fame Points: 3455 More actions · Answer 2

Thanks.

I have and they are - Which is really weird!

The story goes... I allocated only 4 MB to this instance. Realising my mistake I then accessed the instance using the following command; SQLCMD -S SERVERNAME\INSTANCENAME -E. Once in I updated the max memory setting to 4026.

Now I can't access the instance through SSMS.

Any idea?

--------------------------------------------

Laughing in the face of contention...

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Are you able to access from other server?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

arrjay Hall of Fame Points: 3455 More actions · Answer 4

No. I've tried from my machine and a colleagues machine but still can't access the instance.

TCP/IP is enables also

--------------------------------------------

Laughing in the face of contention...

arrjay Hall of Fame Points: 3455 More actions · Answer 5

Oh, this is interesting. Every time i start the services.... it starts then stops! Anyone experienced this before?

--------------------------------------------

Laughing in the face of contention...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Start up failure. Check the latest error log and see why the start up is failing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arrjay Hall of Fame Points: 3455 More actions · Answer 7

Thanks. I've investigated a further and found the following error message; There is insufficient system memory in resource pool 'internal' to run this query.

I have calculated the memory usage and there should be about 6GB for the OS. I think the issue here is that I have allocated memory which the OS thinks it's for itself. I probably need to bounce the server so the the OS and SQL can adjust to the memory that is now available.

--------------------------------------------

Laughing in the face of contention...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

No, that has nothing to do with the OS's memory.

Can you post the error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arrjay Hall of Fame Points: 3455 More actions · Answer 9

Absolutely. Here is a section of logs once I start the service. (The db named instance is Duke)

--------------------------------------------

Laughing in the face of contention...

free_mascot One Orange Chip Points: 27169 More actions · Answer 10

Check the following article:

http://social.technet.microsoft.com/Forums/sqlserver/en-US/3fdf1afb-7171-427c-a01c-2f29caaf3d00/how-to-fix-there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query?forum=sqldatabaseengine

Also ask system admin to check if any issue with VMWare and check physical memory allocated to the server in VMWare correctly.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

humbleDBA Hall of Fame Points: 3490 More actions · Answer 11

arrjay (4/1/2014)
...(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)...

Apart from the memory errors you're getting, as your message states...its trying to connect through Named Pipes, and not TCP/IP. Try selecting TCP/IP for connecting with SSMS or enable Named Pipes if its not enabled already.

I tend to run on TCP/IP only...disabling Named Pipes is another attack vector closed off.

Edit: fixed grammatical errors :blush:

arrjay Hall of Fame Points: 3455 More actions · Answer 12

Thanks all - The named pipes/TCP-IP solution hasn't worked and I can't see anyhting that's going to help me in the link but thanks for helping out!

Thankfully its just a developer instance so I can blow it away and recreate it.

Thanks!

--------------------------------------------

Laughing in the face of contention...