SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Memory Usage & Refused Connections
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

martinch
Starting Member

United Kingdom
35 Posts

Posted - 01/13/2005 :  08:12:05  Show Profile  Reply with Quote
Our database server is running SQL Server 2000 under Windows 2003 Server (the box has multiple Xeon P4s and 3Gb RAM), and has several databases stored on it. We also have a separate web server, again running Windows 2003 Server, and again running several web sites.

When one particular site comes under heavy load (100-200 concurrent users), the we start getting connection denied errors:
"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. "
Obviously, this is a totally helpful error...

I don't think that it is the number of users, as we have a logging system logging every page access to our web sites, which is typically running at around 200-400 concurrent users. Plus, we never used to get this problem when running Windows 2000 - it's only appeared since upgrading to Windows 2003 (and site loading hasn't changed).

Looking at Performance Monitor, whenever this error happens, the number of active connections is very high (>100), but number of transactiosn, etc, is very low (<10).

Any ideas? (Googling for this error makes it seem like this is a problem with Windows 2003, as there are numerous mentions of it).

Also, does anyone know if/how you can see how much memory (not disk space) each database is currently using? (we'd like to see how much RAM each one takes at a given time, as the amount of RAM SQL Server is using is massively greater than the size of all the DBs...)

Thanks!

martinch
Starting Member

United Kingdom
35 Posts

Posted - 01/18/2005 :  05:57:41  Show Profile  Reply with Quote
No takers?
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/18/2005 :  18:56:00  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
martinch, what do you get if you run this:

select *
from master..sysconfigures
where config = 103

That will tell you how many maximum connections you can have to the server. If it is set to 0 then that means 'no limit'. If set to something else, maybe you are exceeding the limit.

As for the memory issue, SQL Server will use as much memory as is available whenever it needs it. Activities like sorting can cause that to jump to multiples of what you might otherwise expect. This is one reason why it is recommended to run SQL Server on a server by itself so that it has access to as much memory as possible. Once SQL Server has taken hold of memory it rarely gives it up. Is this causing you some particular problem or did it just "look weird"?

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.
Go to Top of Page

martinch
Starting Member

United Kingdom
35 Posts

Posted - 01/19/2005 :  04:04:57  Show Profile  Reply with Quote
AjarnMark, thanks for the reply!

quote:
martinch, what do you get if you run this:
select *
from master..sysconfigures
where config = 103

It says that "value" is 0, and "status" is 2, so it doesn't look like it's limiting the number of connections...

quote:
This is one reason why it is recommended to run SQL Server on a server by itself so that it has access to as much memory as possible. Once SQL Server has taken hold of memory it rarely gives it up. Is this causing you some particular problem or did it just "look weird"?

Well, SQL Server is on a dedicated box, simply for that reason. We know it normally uses tonnes of memory, but even so, I guess seeing the memory maxed out set off some kind of instinctive "oh dear" reaction in us . The memory usage isn't causing any problems that I'm aware of - we would just liked to have known which DBs were having the memory allocated to them, out of curiosity more than anything.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 01/19/2005 :  16:53:27  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
If you come up with a way to measuring this, I'd be interested. But my WAG would be that tempdb is the one that really uses up memory due to temp tables, sorts, etc.

---------------------------------------------------------------------------------
Infoneering: Information Technology solutions engineered to professional standards.
Go to Top of Page

martinch
Starting Member

United Kingdom
35 Posts

Posted - 01/20/2005 :  04:42:40  Show Profile  Reply with Quote
Thanks for the reply, AjarnMark! If I ever find out how to get memory usage by DB, I'll let you know (although I get the distinct feeling it's not possible...)

Has anyone else experienced the "DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied." error for seemingly no apparent reason? I ask because we didn't get it on our Windows 2000 server, but we do now that it's been upgraded to Windows 2003. As the only thing that's changed is the OS, does anyone know what could be causing this?
Go to Top of Page

afflack01
Starting Member

2 Posts

Posted - 04/26/2012 :  03:02:19  Show Profile  Reply with Quote
The only difference that it may have from the other machines on the network is that it may have extra memory, a higher speed processor and a bigger hard disk drive. In very large computer setups, there are server machines, which are different from the ordinary desktop. The difference is that they may have more than a single processor. They may have two or more processors. The hard disks of a server machine maybe SCSI hot swappable disks, or fixed hard disks.

unspammed

Edited by - afflack01 on 04/26/2012 03:57:08
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000