471,046 Members | 935 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,046 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 3928
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

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.