468,784 Members | 1,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,784 developers. It's quick & easy.

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 3875
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by s.subbarayan | last post: by
3 posts views Thread by MattC | last post: by
2 posts views Thread by Sidharth | last post: by
22 posts views Thread by Zen | last post: by
reply views Thread by Jeff | last post: by
5 posts views Thread by breal | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.