I've seen queries for 2005/2008 to find what process caused a delete but have not seen anything for 2000. Is there anything?
A:
Dropped a table, view etc. or deleted a row from a table?
Are you looking for an ongoing solution or to do a post-mortem?
Joel Mansford
2009-06-22 15:27:46
ongoing solution. We aren't using transactions so I don't know that we could do post-mortem.
suedeuno
2009-06-22 15:34:30
it would be on deleting a row.
suedeuno
2009-06-22 15:37:08
A:
If you're talking about a current statement or process....
execute:
sp_who2
Eric
2009-06-22 16:13:05
+2
A:
For deletes, you can set up a DML trigger (see CREATE TRIGGER) in BOL for details. Likely the most relevant information that you can obtain will come from sysprocesses, and you can query it using your spid (@@SPID).
Capture the info you're looking for, and log it out to a table you create for logging (along with the key values required to know which record(s) were deleted).
Aaron Alton
2009-06-22 16:25:21
Can I get the spid of the process that deleted the record or will @@SPID give me a different spid?
suedeuno
2009-06-22 20:40:32
@@SPID will return the spid of the session that deleted the row (which is what you're looking for). Here's an example:CREATE TABLE TestSpid (Col1 int)GOCREATE TRIGGER TestTrig ON TestSpid FOR DELETEASBEGIN SELECT @@SPIDENDGOINSERT TestSpid(Col1)SELECT 1GOSELECT @@SPIDDELETE TestSpid
Aaron Alton
2009-06-23 02:06:00