SQL Server could not spawn process_loginread thread

Hi there

I'm running SQL Server 2000 SP 4 (8.00.2162) Standard on Win 2003 SP1, 2GB
RAM, dual 3.00 GHz hyperthreaded processors. I get the above message
intermittently - up to once a week. I have to kill the sqlservr.exe process
to enable SQL Server to restart (or reboot). I cannot connect to SQL Server
when these messages occur.

Sometimes it's preceeded by a "Failed to reserve contiguous memory of Size=
65536" message in the SQL Error Log and jobs failing (in particular, I notic
e
backup jobs failing).

I use the "MaxTransferSize = 524288" option in my backup statements to
reduce the amount of contiguous virtual memory required by my backup jobs
(the issue was occuring before I changed the backup jobs to use this option)
.
Also, in my startup parameters, I have specified -g512 to specify the amount
of virtual address space SQL Server will leave for memory allocations.

We do use a number of com objects in our code, but I'm not sure exactly
where all of them are called from - most of the jobs/coding that have been
set up were done so before I joined. I have tried to use Profiler to track
occurances of '%sp_OACreate%' for SQL:StmtCompleted and SP:StmtCompleted
event classes, but to no avail (no textdata is output).

Firstly, I'm wondering if anybody has a similar issue to mine, and if they
have managed to resolve it. Also, would anyone have any ideas how to further
troubleshoot this issue.

Many thanks

Steve

Hi Steven

Did you see http://support.microsoft.com/defaul...b;en-us;903002?

John

"Steven_Webster" wrote:

> Hi there
>
> I'm running SQL Server 2000 SP 4 (8.00.2162) Standard on Win 2003 SP1, 2GB
> RAM, dual 3.00 GHz hyperthreaded processors. I get the above message
> intermittently - up to once a week. I have to kill the sqlservr.exe proces
s
> to enable SQL Server to restart (or reboot). I cannot connect to SQL Serve
r
> when these messages occur.
>
> Sometimes it's preceeded by a "Failed to reserve contiguous memory of Size
=
> 65536" message in the SQL Error Log and jobs failing (in particular, I not
ice
> backup jobs failing).
>
> I use the "MaxTransferSize = 524288" option in my backup statements to
> reduce the amount of contiguous virtual memory required by my backup jobs
> (the issue was occuring before I changed the backup jobs to use this optio
n).
> Also, in my startup parameters, I have specified -g512 to specify the amou
nt
> of virtual address space SQL Server will leave for memory allocations.
>
> We do use a number of com objects in our code, but I'm not sure exactly
> where all of them are called from - most of the jobs/coding that have been
> set up were done so before I joined. I have tried to use Profiler to track
> occurances of '%sp_OACreate%' for SQL:StmtCompleted and SP:StmtCompleted
> event classes, but to no avail (no textdata is output).
>
> Firstly, I'm wondering if anybody has a similar issue to mine, and if they
> have managed to resolve it. Also, would anyone have any ideas how to furth
er
> troubleshoot this issue.
>
> Many thanks
>
> Steve

Thanks John.

I'll run profiler to determine the packet size and let you know.

Many thanks

Steven

"John Bell" wrote:

> Hi Steven
>
> Did you see [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;903002?[/url
]
>
> John
>
> "Steven_Webster" wrote:
> 

Hi John

It appears that the connections use a packet size of 4096 bytes (although
there is one .NET application which uses packet sizes of 8192 bytes. This
isn't a heavily utilized app though, with only a handful of connections sinc
e
this morning). I was wondering, would you have any tips maybe on pinpointing
memory related issues? Like I said, the issue is intermittent. It did occur
again this morning, preceded by the transaction backups failing, but I don't
think this is the root cause of the issue. There are 28 user databases on
this server, with many different front ends connecting to them (mainly web
front ends). I've got data from counter logs that I have set up that may be
able to help me, but I'm not sure even where to start with them or even if
I've captured the correct data. Any help would be greatly appreciated.

Many thanks

Steven

"John Bell" wrote:

> Hi Steven
>
> Did you see [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;903002?[/url
]
>
> John
>
> "Steven_Webster" wrote:
> 

Hi Steven

Sorry that I have not got back to you earlier, this one slipped under the
radar!

Trapping this problem may be very difficult, you may want to look at PSSDIAG
to see if that helps
http://www.microsoft.com/downloads/...&displaylang=en

Make sure that you are not running any unnecessary processes/services, if
there are parts of the system that can be transferred elsewhere you could
eliminate them, but using an alternative server. This would also help if you
want to re-create an environment where you may want to try and test the
application.
If you want to do this then ACT may help check out
http://msdn.microsoft.com/library/d...
ethowto11.asp
and
http://msdn.microsoft.com/library/d...
ethowto11.asp
also for stress testing the database look at
http://www.microsoft.com/downloads/...&displaylang=en

John

"Steven_Webster" < StevenWebster@discus
sions.microsoft.com> wrote in message
news:48528583-2112-4193-B71D- 3EF7B4996F84@microso
ft.com...
> Hi John
>
> It appears that the connections use a packet size of 4096 bytes (although
> there is one .NET application which uses packet sizes of 8192 bytes. This
> isn't a heavily utilized app though, with only a handful of connections
> since
> this morning). I was wondering, would you have any tips maybe on
> pinpointing
> memory related issues? Like I said, the issue is intermittent. It did
> occur
> again this morning, preceded by the transaction backups failing, but I
> don't
> think this is the root cause of the issue. There are 28 user databases on
> this server, with many different front ends connecting to them (mainly web
> front ends). I've got data from counter logs that I have set up that may
> be
> able to help me, but I'm not sure even where to start with them or even if
> I've captured the correct data. Any help would be greatly appreciated.
>
> Many thanks
>
> Steven
>
> "John Bell" wrote:
> 

Hi

You may also want to look at
http://support.microsoft.com/kb/316749/EN-US/

John
"Steven_Webster" < StevenWebster@discus
sions.microsoft.com> wrote in message
news:48528583-2112-4193-B71D- 3EF7B4996F84@microso
ft.com...
> Hi John
>
> It appears that the connections use a packet size of 4096 bytes (although
> there is one .NET application which uses packet sizes of 8192 bytes. This
> isn't a heavily utilized app though, with only a handful of connections
> since
> this morning). I was wondering, would you have any tips maybe on
> pinpointing
> memory related issues? Like I said, the issue is intermittent. It did
> occur
> again this morning, preceded by the transaction backups failing, but I
> don't
> think this is the root cause of the issue. There are 28 user databases on
> this server, with many different front ends connecting to them (mainly web
> front ends). I've got data from counter logs that I have set up that may
> be
> able to help me, but I'm not sure even where to start with them or even if
> I've captured the correct data. Any help would be greatly appreciated.
>
> Many thanks
>
> Steven
>
> "John Bell" wrote:
> 

Hi John

Thanks for getting back to me. I'm in the process of moving a particular
database to a new server. I've got a feeling that this is the one that's
causing the issue (the problems started soon after this db went live). It
queries a couple of Oracle linked servers, plus there's a DTS that's run
every 2 hours that takes nearly 1 hour to complete that connects to an Oracl
e
server using Microsofts OLE DB Provider for Oracle. There's some very strang
e
things happening with this server. Today, I couldn't ping it or ping from it
,
yet SQL Server was working fine, and there appeared to be no problem with th
e
network card.

Thanks for your help. I'll let you know how I get on with your suggestions.

Kind Regards

Steven

"John Bell" wrote:

> Hi Steven
>
> Sorry that I have not got back to you earlier, this one slipped under the
> radar!
>
> Trapping this problem may be very difficult, you may want to look at PSSDI
AG
> to see if that helps
> http://www.microsoft.com/downloads/...&displaylang=en
>
> Make sure that you are not running any unnecessary processes/services, if
> there are parts of the system that can be transferred elsewhere you could
> eliminate them, but using an alternative server. This would also help if y
ou
> want to re-create an environment where you may want to try and test the
> application.
> If you want to do this then ACT may help check out
> http://msdn.microsoft.com/library/d...enethowto11.asp
> and
> http://msdn.microsoft.com/library/d...enethowto11.asp
> also for stress testing the database look at
> http://www.microsoft.com/downloads/...&displaylang=en
>
> John
>
> "Steven_Webster" < StevenWebster@discus
sions.microsoft.com> wrote in messag
e
> news:48528583-2112-4193-B71D- 3EF7B4996F84@microso
ft.com... 
>
>
>

Unfortunately, this can be caused by different things but here are a few
things you can look at...

1) The CSRSS.exe.  There is bug with the CSRSS.exe using more memory over
time and may cause your SQL instance not to respond or start.  Here's the KB
article -  http://support.microsoft.com/kb/316749/EN-US/

Just use Task Manager to verify this problem.  At most, the CSRSS.exe should
only be using 10MB of memory.

2) You mentioned the SP_OA* and Oracle linked server calls...

2a. If you have a lot of SP_OA calls, to many of these calls may consume or
fragment SQL server's memory and trigger the memory error.  You can move
these out of the SQL server process space by specifying the context it shoul
d
run when the SP_OAcreate is called.

From BoL:
sp_OACreate { progid | clsid } , objecttoken OUTPUT [ , context ]

context
Specifies the execution context in which the newly created OLE object runs.
If specified, this value must be one of the following:

1 = In-process (.dll) OLE server only.

4 = Local (.exe) OLE server only.
** a context of 4, moves it out of the SQL Server Process Space.

5 = Both in-process and local OLE server allowed

*** Be sure to test that the SP_OA's return the same results as before
because we ran into a bug with one calling an XML object and it failed to
return the correct results once it was moved out-of-process.

****To find all the sp_OACREATE's on your instance, run -
exec sp_MSforeachdb 'print ''?'' use ? select  object_name(id),id  from dbo.
syscomments where text like ''%sp_OACreate%'''


2b. For the Oracle Linked server calls, you can change the provider options
for the MS OLE DB provider for Oracle to move the linked server calls out of
the SQL Server's process space.  Since the 'Provider Option' button on
existing Linked Server's properities will be gray'ed out, you'll need to do
the following to check or change this option,

1- you create a 'new linked server' (call it whatever you like)
2- Click on Provider options
3- 'Allow Inprocess' option will be checked by default
4- Uncheck the 'Allow Inprocess' option
**This will change the all linked servers using this provider to out-of-
process.
5- Save the linked server (you can delete the 'Test' linked server after and
change will be kept.)

...

Those were some of the hoops we had to jump through to try and solve this
memory problem, as well as, increase the System Global area using '-g'
startup parameter.

Here is a good KB article describing SQL server's memory -
http://support.microsoft.com/?id=271624

I encountered this problem with a SQL instance on a Windows 2003 Enterprise
Ed., with 8 2Ghz cpus and 32GB of RAM.  Our fabrication team has this
server/SQL instance running at 100% capacity 24x7 fabricating and prepping
our online systems data.

Anyway, I hoped I helped...  Have a good one.

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200602/1

Hi Steven

Problems with the Network can be hard to diagnose. Changing the card
completely and attaching to a different hub may go most of the way to ruling
them out.

John

"Steven_Webster" < StevenWebster@discus
sions.microsoft.com> wrote in message
news:CD88CBB3-DBC9-4B83-9544- 90CECF957F2C@microso
ft.com...
> Hi John
>
> Thanks for getting back to me. I'm in the process of moving a particular
> database to a new server. I've got a feeling that this is the one that's
> causing the issue (the problems started soon after this db went live). It
> queries a couple of Oracle linked servers, plus there's a DTS that's run
> every 2 hours that takes nearly 1 hour to complete that connects to an
> Oracle
> server using Microsofts OLE DB Provider for Oracle. There's some very
> strange
> things happening with this server. Today, I couldn't ping it or ping from
> it,
> yet SQL Server was working fine, and there appeared to be no problem with
> the
> network card.
>
> Thanks for your help. I'll let you know how I get on with your
> suggestions.
>
> Kind Regards
>
> Steven
>
> "John Bell" wrote:
> 

Hi John - thanks for the reply. The server's actually been stable for just
over a week now. As per one of the pointers that Lutzdw sent onto me, I
unchecked the AllowInProcess option for the MSDAORA Provider and so far so
good. Also, I moved a DTS that does a fairly large select from an Oracle
database using the same provider to a new server and added the extended
property NoWorkerThread. Mind you, it still connects to the original server
to transfer data to, but the dtsrun.exe is running on the new server. I'm
keeping a close eye on it, and I'll keep you updated.

Many thanks for your help on this - much appreciated.

Regards

Steven

"John Bell" wrote:

> Hi Steven
>
> Problems with the Network can be hard to diagnose. Changing the card
> completely and attaching to a different hub may go most of the way to ruli
ng
> them out.
>
> John
>
> "Steven_Webster" < StevenWebster@discus
sions.microsoft.com> wrote in messag
e
> news:CD88CBB3-DBC9-4B83-9544- 90CECF957F2C@microso
ft.com... 
>
>
>