SQL Server Performance Forum – Threads Archive

Multitude of locks

I have a db that has two applications accessing it. One is a C++ program and the other is Crystal Reports. I believe that i have a Crystal report that is causing a bunch of locks on the db and then causing my c++ application to hang until I stop/start MSSQL again. I’m not very familiar with locks, and was wondering if someone could help me out on why they happen, and how to resolve them. I realize that locks are normal, but i don’t know why a lock will never unlock. When i looked at the locks (while my application was hanging) i found several hundred. Here’s the tsql i found for each lock: SELECT "PgmFunction"."Description", "CartonHistory"."DateTimeStamp", "Usr"."Name", "CartonHistory"."UserID", "CartonHistory"."CartonID", "CartonHistory"."Description" FROM ("cryrep"."dbo"."CartonHistory" "CartonHistory" INNER JOIN "cryrep"."dbo"."PgmFu and the lock is of type PAG with mode S. Any help is greatly appreciated. Thanks, Ben

Confirm the service pack levels for more information.
Confirm the SQL memory settings in this case. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

The service pack is SP4 for SQL Server 2000. I’m not sure what you’re asking about the memory settings. I have 1.5Gb of RAM on the server, and the Memory tab of SQL has "Dynamicaly configure SQL Server Memory" checked. Let me know what else you need to know. I don’t know how to debug this type of thing. Thank you very much, Ben

Run SP_WHO2 and see what type of locks are assigned to that transaction.
refer to the books online for table hints to reduce the locking. http://www.sql-server-performance.com/reducing_locks.asp
http://www.sql-server-performance.com/at_sql_locking.asp
http://techrepublic.com.com/5100-6329_11-5181472.html Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

How many rows you get running that query? How much time does it take to execute this query?

]]>