Query Analyzer gets disconnected, sleeping connections?

Posted on 2006-05-24
Medium Priority
Last Modified: 2006-11-18

For the second time we are running in to this problem:

We have a database server running MS SQL Server. We have about 5 main databases running. We have a webserver running web based applications and a few workstations running 'normal' app's that all connect to the database server.

For some unclear reason at a certain moment I can not seem to connect to the database using the query analyzer. I connect normaly (SA user + password), I get my SQL query window, but as soon as I try to execute a query I get a new logon screen. If I then log on again, I get a blank query window, and so on...

Other processes (e.g. web pages using the database server) seem to work fine.

Restarting the SQL service will solve my problem... but it is far from a good solution.

I'm not a star DBA, but I began my search through the enterprise manager's reports. Using the 'Process Info' I find that there are a huge amount of sleeping processes (about 300!). Is this something to worry about, and could it have anything to do with my logon troubles in the query analyer.

Please help me
Question by:palloquin
  • 3
LVL 28

Expert Comment

ID: 16750668
Wht is existing RAM on your server?
Pls. check the cup and memory usage of your server when this problem occurs. for sure this looks hardware issue.

Accepted Solution

pdvd earned 1000 total points
ID: 16751295
We hav 1.5 GB's of RAM in the machine, it is allways completely in use.
CPU load is always about 50%

You think this is a hardware related problem?
LVL 28

Expert Comment

ID: 16774476
Usually Yes, because you are not facing this problem always. so next time if the problem exists check the event log on server for sql.

Author Comment

ID: 16775130
Been away, sorry.

Same issues here. Dual XEON at 1233 and nearly 2Gb's memory. The memory fully used always. SQL is the biggest hog, it uses about 90% of the memory. CPU loads vary between 40 and 90% constantly.

It there a basic rule that enables me to calculate the amount of memory needed for a certain size DB?

Thanks so far!
LVL 28

Assisted Solution

imran_fast earned 1000 total points
ID: 16775386
>It there a basic rule that enables me to calculate the amount of memory needed for a certain size DB?
not nessasary but depend on the no. of user using the sql. the no. of hits on sql are the major issue.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question