Looking for a way to find a process/trigger/whatever that is removing entries from a table

Question

Looking for a way to find a process/trigger/whatever that is removing entries from a table

roy.tollison

SSCommitted

Points: 1930
More actions
June 1, 2016 at 11:06 am

#308153

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

Have you tried running the SQL Profiler?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

roy.tollison SSCommitted Points: 1930 More actions · Answer 2

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

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

roy.tollison SSCommitted Points: 1930 More actions · Answer 4

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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

drew.allen SSC Guru Points: 76985 More actions · Answer 6

Using SQL Profiler for traces has been deprecated. You should start using Extended Events.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

roy.tollison SSCommitted Points: 1930 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)