Locking is a part of every SQL Server database. It’s critical functionality and without it, we could not have multiple people working in the same database, at the same time. It ensures data integrity, but can also cause some errors.
Example:
I was assisting a customer who found their Dynamics GP system was locked up quite often. They weren’t sure why, so I had to do a little investigating.
First, I wanted to find out which table in SQL has a lock, so I ran the following script:
SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.TABLE NAME');
Next, I wanted to find out which “owner” had the lock on that table. So I did this:
SP_WHO2 and match the spid with the request_session_id
I found out it was a Scribe Integration locking the tables. Turns out, the integration was taking far too long, over 4 hours.
I had to remove the locks, by using the following command:
KILL with the number in of the above query request_session_id
And lastly, I adjusted the integration so it took less time.
These SQL commands can be used for any program using SQL Server. Many times I see locks happen in batches, invoices, and sales orders. Also, sometimes if people do not log off, that can cause locking problems too.
If you need further assistance, email us at Support@Crestwood.com
