473,386 Members | 1,791 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

Locked process hang ups not in error logs?? Memory usage too??

Details:
MS SQL 2000 dual Intel 1.2 GHz processors.
1 GB RAM
2.1 GB dB
Dynamic Memory Managment.
No other apps running on this server.

First question:
Since I have Dynamic Memory Managment setup, Is it usuall that the
sqlsrv.exe process on the server steadily climbs and is in the 800 to
900 MB range. There is only about 20 MB free. In theory this is how
DMM can work, but do people really see it work this way.

Second question:
I had users complaining about lockups in the app I have to support
that connects to this dB. At first I thought it was the large use of
memory, but once I was able to see in Enterprise Manager that there
was process blocking several other processes. EM then locked up and I
couldn't get to the details of what the exact process was that was
doing the blocking. After restarting SQL services things were fine.
When I checked the logs there was nothing there about a hung process.
The logs seemed very sparse. Why would there not be anything in the
logs about it. The logs actually seem very thin on any information.

Thanks,

T.
Jul 20 '05 #1
1 4052
D. Buck (tr*******@yahoo.com) writes:
First question:
Since I have Dynamic Memory Managment setup, Is it usuall that the
sqlsrv.exe process on the server steadily climbs and is in the 800 to
900 MB range. There is only about 20 MB free. In theory this is how
DMM can work, but do people really see it work this way.
This is perfectly normal. The more data SQL Server can hold in cache, the
better chances for a swift response to queries.
Second question:
I had users complaining about lockups in the app I have to support
that connects to this dB. At first I thought it was the large use of
memory, but once I was able to see in Enterprise Manager that there
was process blocking several other processes. EM then locked up and I
couldn't get to the details of what the exact process was that was
doing the blocking. After restarting SQL services things were fine.
When I checked the logs there was nothing there about a hung process.
The logs seemed very sparse. Why would there not be anything in the
logs about it. The logs actually seem very thin on any information.


SQL Server does not log blocking situations. You can set some trace
flags to log deadlock situations, but the situation you describe does
not seem to have been a case of deadlock.

EM is not a very good tool to analyse blocking situations, because if
there is a process that has created a temp table in a transaction, that
will block a stored procedure that EM uses.

It's better to use the stored procedures sp_who and sp_who2, together
with sp_lock and DBCC INPUTBUFFER to see what is going. Sometimes this
information is sufficient, but at times it can be difficult to digest,
because there is a lot of numeric object ids that you need to translate.
As an alternative, I offer aba_lockinfo, which gathers snapshot information
about a blocking situations, so that you can quickly identify the
offending processes and kill these without having to stop SQL Server.
You can also analyse the output later, to see what the cause for the
blocking was. You find aba_lockinfo at
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: EP | last post by:
Hello patient and tolerant Pythonistas, Iterating through a long list of arbitrary (and possibly syntactically flawed) urls with a urllib2 pinging function I get a hang up. No exception is...
2
by: w2jin | last post by:
Hi All Some my SQL Server are experience high memory usage. 1. How can I detect which process which process cause the big memory usage and not released? 2. Which sql server components in...
6
by: s.subbarayan | last post by:
Dear all, I am currently working on a project where we are asked to display the error logs on the hyperterminal on the press of the key.Typically the log should collect all the error messages and...
3
by: MattC | last post by:
I have an ASP.NET app that is running at around 60MB for the w3wp.exe. This often jumps very quickly to 100/120 MB before shrinking again. It is also using around 250MB of virtual memory. Is...
2
by: Sidharth | last post by:
Hello, We are experiencing some issues with the aspnet_wp process on our live servers. The problems are intermittent and we cannot reproduce it on our dev and test servers. Currently around...
35
by: Alex Martelli | last post by:
Having fixed a memory leak (not the leak of a Python reference, some other stuff I wasn't properly freeing in certain cases) in a C-coded extension I maintain, I need a way to test that the leak is...
22
by: Zen | last post by:
Hi, My production machine has 2G of memory, when aspnet_wp.exe goes up to about ~1.2G of memory usage, I start get out-of-memory exception. Other processes don't use as much memory and I added...
0
by: Jeff | last post by:
Banging my head against the wall trying to figure this out. I have a 'sometimes' hang while disposing a form which contains an ActiveX control (Flash.ocx version 7). The form's Dispose() method...
5
by: breal | last post by:
I have a db table that holds a list of ports. There is a column in_use that is used as a flag for whether the port is currently in use. When choosing a port the table is read and the first...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.