Query Analyzer gets disconnected, sleeping connections?

Posted on 2006-05-24
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
    LVL 28

    Expert Comment

    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

    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

    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

    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

    >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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now