SQL Larry


One of my production SQL Server 2000 systems is listening on TCP and Named Pipes, but not on Shared Memory.

This server has a lot of scheduled jobs that are internal to this box. I assume these jobs would benefit from using shared memory instead of TCP/IP, but I can't figure out why it doesn't use shared memory already and how to correct that.

Thanks in advance for all assistance.





Re: SQL 2000 Not listening on Shared Memory. Why not?

Jag Sandhu


Hi SQL Larry,

How do you know that SQL is not listening on shared memory

Does it log a message in the errorlog that it is only listening

What trace flags have you got set for this instance

Could you try and connect using the following, see if you can connect.

osql -E -S lpc:<Servername>

It is L not i in. Please excude the < and >

regards

Jag





Re: SQL 2000 Not listening on Shared Memory. Why not?

SQL Larry

Jag,

Thanks for your response. I should have mentioned that I was viewing the errorlog. For this server it indicated it was listening on TCP and Named Pipes. For different server, the errorlog showed TCP, Shared Memory, and Named Pipes.

The osql command made a successful connection, but the connection shows as TCP/IP in the Net_Library column of sysprocess for the program_name "OSQL-32".







Re: SQL 2000 Not listening on Shared Memory. Why not?

Jag Sandhu

Hi Larry,

OK. I was wondering if you executed the command remotely from client machine rather than the server itself.

Could you try and run the following on the database server iteself.

osql -E -S lpc:<Servername> -Q"select net_library from sysprocesses where spid=@@spid"

It should return the following if it is listening on shared memory.

net_library
------------
LPC

regards

Jag






Re: SQL 2000 Not listening on Shared Memory. Why not?

SQL Larry

Hi Jag,

Thanks for continuing your assistance. The original query was run on the server in question, as was the following. I changed the server name shown below for security reasons.

Thanks,

Z:\>osql -E -S lpc:myserver -Q"select net_library from sysprocesses where spid=@@spid"


net_library
------------
TCP/IP

(1 row affected)






Re: SQL 2000 Not listening on Shared Memory. Why not?

Jag Sandhu

Hi Larry. No problems.

Thanks for posting the output. Can you check the Registry if the following key exists.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Lpc

Also please check that the Service account that SQL Server uses has FULL CONTROL permissions on the Key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\

The LPC key should be deleted when SQL Server is stopped and should be created when SQL Server starts again. If the LPC key is not being deleted then SQL Server is not able to create it again when restarting and consequently not able to listen on shared memory.

In addition could you please try and stop SQL server service and check if the LPC is gone.

and start SQL Server service to see if LPC key is recreated.

lets get this thing fixed.

Jag






Re: SQL 2000 Not listening on Shared Memory. Why not?

Dhericean

Can you run up the SQL Server Client Network Utility on the server (in the SQL Server Program group) and just check that shared memory protocol has not been disabled on the server. It is a checkbox on the General tab of the utility dialog.

If it has been then you might want to try and find out why before enabling it. If it was required for some kind of security hardening you will need permission before reenabling it (Making changes that are perceived as opening a production server up to attack can be a sacking offence in a number of places).





Re: SQL 2000 Not listening on Shared Memory. Why not?

Jag Sandhu

Hi Dhericean,

You are right we should check the client utility. However this problem seems to be with the server side because SQL error log doesnot say that it is listening on Shared Memory.

regards

Jag






Re: SQL 2000 Not listening on Shared Memory. Why not?

Dhericean

I believe that running the client utility on the Server is how you turn off the shared memory protocol for the server (the shared memory protocol enabled checkbox does not make any sense on any box other than a server). It is basically used to configure any interprocess communication with the server and I imagine if you turn off the Shared Memory Protocol that turns off the relevant Net Library for the server.

I am not sure that this is possible for SQL Server 2005 but this case refers to SQL Server 2000 (for which it is definitely possible).





Re: SQL 2000 Not listening on Shared Memory. Why not?

SQL Larry

Thanks Jag and Dhericean for your continuing suggestions.

Shared memory is enabled in the client network utility. Nevertheless, I tested this on a 3rd server. Regardless of the client network utility setting for shared memory, the SQL Server on my desktop system (version 8.00.194) starts up listening on TCP, Shared Memory, and Named Pipes.

I had previously inspected the registry settings for both the ailing server and another that is using shared memory properly. However, I wasn't sure which keys affected which functions. The Lpc key exists at the specified registry location for both, but I noticed version differences.

The ailing server is at SP3a (8.00.818) as is its O:\Program Files\Microsoft SQL Server\MSSQL\Binn\ssmslpcn.dll file.

The server working properly is at SP4 (8.00.2040) and ists ssmslpcn.dll file is 8.00.2039.

There is a reference to a shared memory bug in the bug fix list for SP4 (http://support.microsoft.com/ kbid=888799). The bug is briefly described at http://support.microsoft.com/kb/830366/EN-US/. I haven't seen such errors, and this sounds like something that would occur after SQL Server is already running. The errorlog indicates that share memory is not used from the onset.

2007-04-10 09:44:14.51 server SQL server listening on TCP, Named Pipes.

Regarding the LPC key, I tested this on my desktop system. The errorlog indicates the server is listening on shared memory, but the LPC key is missing when the server is on or off.

So, I still puzzled.

Thanks again, and keep your ideas coming.






Re: SQL 2000 Not listening on Shared Memory. Why not?

oj

This is a bug in SP3 and has been fixed in SP4 for sql2k.
http://support.microsoft.com/kb/815249







Re: SQL 2000 Not listening on Shared Memory. Why not?

SQL Larry

Yep. That sounds like the problem. Thanks a lot.