Connection sleeping

If an MS SQL 2005 Connection is sleeping in the activity monitor, does it mean it's not closed? I'm pretty sure it's closed, just want to confirm.

Lately, our DB server got hammered with requests and it reached max-pool size (we implemented caching after that).

On MS SQL 2005 if you close the connection, you'll see it as sleeping waiting for you to open a new connection, when you do that MS SQL 2005 will give you one of those sleeping connections. Make sure all connections are close on your end.|||

I have not found this to be true. I have a method which opens a connection, runs a SQL statement, then closes the connection. If I cal this method quickly, hundreds of times (such as in a loop), then I spawn hundreds of connections (which all appear to be sleeping) and the pool size is exceeded and everything locks up. There do not appear to be any connection leaks (the .Close() method is always called).

The very interesting thing is that this only happens in IIS -- it does not happen in Casini (the "built-in" ASP.NET server within Visual Studio). In Casini the connections seem to be handled much better -- this tells me it is not a SQL Server issue but may be an IIS connection pool handling issue.

Does anyone have any thoughts?

Thanks everyone,

Matt

Connection Problems

Hi,

I am new to SQL Server 2005 but have a pretty good understanding of 2000, but I am struggling with connections! I have created a .Net Windows application (installed on an XP box) that should talk to a SQL Server 2005 database which is installed on a 2003 server. The desktop app is installed is in one domain (DOM1), and the SQL server 2005 is in another domain (DOM2). I have created a active directory user in DOM2 which will be a service account which manages the connections between the app and the db. I am using the following connection string:-

Server=ZZLUKGKK1M1J\INST2005;Database=PublicationWizard;User ID=DOM2\zzPubWizardDBUser;Password=password;Network Library=DBMSSOCN;Trusted_Connection=False;

When I try and connect an error is thrown, and I get the following in the SQL log

Error: 18456, Severity: 14, State: 5.

Login failed for user 'DOM2\zzPubWizardDBUser'. [CLIENT: 10.80.150.121]

I know that the user name and password are correct and that it exists with the DOM2 domain. I have also included "Network Library=DBMSSOCN" as I was getting problems with it trying to connect via Named Pipes. As I am really used to SQL Server 2000 I might be missing something obvious but I cannot for the life of me figure this out.

Any suggestions will be most welcome as this is driving me nuts

by setting trusted_connection=false you are implying you wish to use SQL Authentication not Windows. Thus your user must be created on the destination sql server and sql authentication must be enabled on the server itself.