1

I have a log table which I have to clean up a bit because it is growing very fast. While deleting some historical data I reached count zero for that condition on which I have deleted the entries. 5 minutes later I check again (hit f5) and there are some old entries (50). I commented every known function in the application which adds records to this table and entries are still popping out.

Using SELECT sql FROM sys.syscacheobjects where sql like '%Table_name%', I found one insert query but I don't know how or where it is being executed from. Looking at the where clause of the insert string I think it might be a stored procedure?

values (@0, @1, null, @2, null, null, @3, @4, @5, @6, @7) 
select [TableId] from [dbo].[Table] where @@ROWCOUNT > 0
ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
ArxXx
  • 51
  • 4
  • So the question is? How to locate the application that sends these INSERT statements? – ypercubeᵀᴹ Apr 03 '14 at 08:32
  • Yes, and also is there a way of tracking only insert statements on that table? I started sql profiler and after 5 minutes new rows appeared in the table but in the profiler all I could fing were select statements – ArxXx Apr 03 '14 at 09:40
  • Well, you could add an INSERT trigger that will deny inserts to the table - and see who complains or what breaks. But that may have unwanted side effects in those applications. – ypercubeᵀᴹ Apr 03 '14 at 09:43
  • 1
    Check this similar problem: http://dba.stackexchange.com/questions/4269/how-to-find-out-who-deleted-some-data-sql-server – ypercubeᵀᴹ Apr 03 '14 at 09:45
  • @ypercube hmm... interesting but cant find the insert for this table I am searching for. I queryed top 1000 results but only 76 were returned – ArxXx Apr 03 '14 at 10:02
  • This answer as well: http://stackoverflow.com/questions/20491928/check-who-performed-insertion-in-table-on-ms-sql-server But I have no idea if these ways can find out inserts performed by triggers in other tables. – ypercubeᵀᴹ Apr 03 '14 at 10:08
  • I dont know if it is a trigger. As far as I can see I commented all entitiy framework inserts for that table. No triggers exist on tables for that table – ArxXx Apr 03 '14 at 10:14
  • I meant an UPDATE trigger on another table that for example inserts a new row in this table when a row is updated in that other table. – ypercubeᵀᴹ Apr 03 '14 at 10:20
  • Already checked that...I went throw all triggers declared on all other tables and none of them is updating or inserting this table – ArxXx Apr 03 '14 at 10:37
  • That looks like an Entity Framework generated parameterised query possibly. (IIRC that uses the @@rowcount pattern shown) You could create an insert trigger on the table and log details such as application name, host name etc to help narrow the search down. – Martin Smith Apr 03 '14 at 15:49

0 Answers0