June 1, 2016 at 11:06 am
i am using a 3rd party vendor software and it has a table that they add rows to when i want to indicate that a specific record is locked in that software. it leaves these rows in the lock table until the user unlocks the record or moves on to another record. I am doing a test and while querying the lock table i see the rows get added for lock status but then something is just deleting everything in the table removing all the rows.
Is there a tool or something that i can user to determine what is removing those rows from the table.
June 1, 2016 at 11:09 am
June 1, 2016 at 11:21 am
downloading it now, but the local SQL admin booted me off the system. not wanting to install anything else. so we are trying to get it installed to find out what is hammering that table.
June 1, 2016 at 12:23 pm
Profiler can connect to SQL Server from off of the server itself, although this can have some performance effect.
The most likely thing here is a "cleanup job" running in SQL Agent, or some other process in their application tier. There could be triggers that do it, or just regular code that is part of the unlock process.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2016 at 12:32 pm
Yeah since it is a production server the Admin didn't want to touch it until we created a test server and installed everything on it. But on the test server the 3rd party application is working fine. rows go into the lock table and clear out when they are supposed to.
I have pushed this back to them to find the culprit on the production server and he is asking for a way to determine the source of the issue. I have pushed back with SQL Profiler. So he and his team are working on installing it but they are adamant that the production server can not have any downtime. I said they could check with MS but that i have not ever seen an issue with installing it. I am guessing that sometime later this week we will get it installed and then start looking for the source of the problem.
I was just hoping for something outside of Profiler so I could the client working properly again.
June 1, 2016 at 12:50 pm
Since you are on SQL 2012 I would look into Extended Events to capture stuff affecting this table.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 1, 2016 at 1:21 pm
Using SQL Profiler for traces has been deprecated. You should start using Extended Events.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 3, 2016 at 12:03 pm
Thanks for all the info/help. We found the issue with the 3rd party sp that was looking for orphaned entries in the lock table based upon the session_id. The users didn't have access to the sysprocesses.
delete from LockTable1 where LockPID not in (select spid from master..sysprocesses)
Well without access it will hammer all the records in LockTable1.
Thanks again
June 3, 2016 at 8:45 pm
roy.tollison (6/3/2016)
Thanks for all the info/help. We found the issue with the 3rd party sp that was looking for orphaned entries in the lock table based upon the session_id. The users didn't have access to the sysprocesses.delete from LockTable1 where LockPID not in (select spid from master..sysprocesses)
Well without access it will hammer all the records in LockTable1.
Thanks again
Without access, it shouldn't do anything but fail.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply