Problem solve Get help with specific problems with your technologies, process and projects.

Memory problems with SQLServerAgent

I have some severe memory problems on SQL Server 2000 for a server that we just built recently. The specs for the machine are Proliant 2.2GHz, 1 GB memory, 80 GB hard drive. Every night the backup and SQLServerAgent jobs fail (not consistently - some fail sometimes), and the server is consuming more and more memory (25 MB --> 800 MB) until it eventually crashes. I am regularly getting errors in the SQLServerAgent error log like "[382] Logon to server 'TOKRDB01' failed (ConnAttemptCachableOp) " or "[298] SQLServer Error: 18452, Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. [SQLSTATE 28000]", which are not easily explainable.

We actually had another server with similar specs that was working fine with the same databases, but the hardware support of the server ran out, and we copied the databases to this new server. One thing that was a little unorthodox was that we tried to keep the computer name the same, so we originally installed the server as XXXnew, then we changed the computer name to XXX and made necessary changes to get things to work. Everything runs fine, other than the memory consumption. I am not sure if this is related.

Can you point me in some directions to troubleshoot this? I've seen many references to strange SQLServerAgent errors, but I haven't seen any good explanation about how to troubleshoot it. One thing is that this server has two processors (but we have a one processor license), so I reset the server properties to work with only one processor. But that doesn't seem to make such difference.

The "not associated with a trusted SQL Server connection" means that you have the security properties on your SQL Server set to integrated only, and you are attempting to use a regular SQL Server login to access. How do you know you have memory issues? What monitoring data have you captured that points to a memory problem? The only issue I can see is that your SQL Server crashes. That does not happen by SQL Server consuming memory because a SQL Server can consume all except 128 MB of memory on any server if it needs the memory, and once it hits that limit will start utilizing the disk drives much more heavily. In no case that I know of does this cause a SQL Server to crash because if it did, thousands of SQL Servers around the world would crash on a daily basis.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.