3

I'm working on a legacy SQL Server application, with close to 3,000 tables and more than 10,000 stored procedures.

One of those stored procedures is deleting some needed data from a table. I don't know which one it is.

I created a trigger on the table which logs all deleted rows in another table, created just for debugging. It would be very helpful if the trigger could also log the name of the stored procedure which invoked it. Is such a thing possible in SQL Server 2008 R2 (v10.50)?

If not, is there any other information available from within a trigger which could help me identify why it was invoked?

1
  • You can use Extended Events to capture the statements that are being executed along with the object names to figure out! Use predicates to narrow down your capture.
    – DenisT
    Aug 6, 2014 at 15:51

2 Answers 2

4

A partial answer to your question, could be to use DBCC INPUTBUFFER, this doesn't necessarily give you the stored procedure that is deleting your records. It can give you the first stored procedure called by the client, ie:

application code calls:

EXEC EntryStoredProc  /* this call is the one that will be identified by DBCC INPUTBUFFER */
...
/* inside EntryStoredProc */
EXEC SecondaryProc
...
/* inside SecondaryProc */
DELETE FROM [MyTable] ...

To add the logging to your trigger, add code similar to:

DECLARE @EventInfo varchar(200)
DECLARE @EventSource TABLE (EventType nvarchar(30), Parameters int, EventInfo nvarchar(4000)) 

INSERT INTO @EventSource
EXEC ('dbcc inputbuffer (' + @@spid + ') with no_infomsgs')

SELECT TOP 1 @EventInfo = SUBSTRING(APP_NAME() + '.' + ISNULL(EventInfo, ''), 0, 200) 
FROM @EventSource

INSERT INTO MyLogTable (ID, EventInfo)
SELECT d.ID, @EventInfo
FROM deleted

Note: This obviously has a performance impact and should not be left on your live server for a long time.

3

Couple of Options:

Option1:

Your best option is to run a server side trace with filtering options enabled.

This way you can narrow down the amount of data captured.

Option2:

Depending on the version (Enterprise), you can use SQL Audit as it allows you to track DELETE, EXECUTE and other Actions on the Object level as well. So you can filter the table that you are interested and enable the audit. Note that SQL Audit behind the covers uses Extended Events.

Option 3: (I have not used this .. but its from msdn)

Light weight SQL Server procedure auditing without using SQL Server auditing version 2


Another option would be to use CONTEXT_INFO as described in this answer. I dont see if this will help you as you will not be modifying legacy code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.