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 " Logon to server 'TOKRDB01' failed (ConnAttemptCachableOp) " or " 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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.