Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sandeepyadavlko
Starting Member
3 Posts |
 Posted - 2011-05-18 : 12:52:32
|
| Hi,Need help to identify what is causing deletion of records from a table.I have a .NET webservice, which works mostly on sp's. I have recently found that there is a table from which certain rows are getting deleted automatically ( i have check my application and sp's there is no deletion code anywhere though this particular table is updated at different stages).I have check SQL Transaction logs and found some delete entries but could not read them and know the root cause of such deletion.I can't use third party software as i too have only remote access to DB.Please help me in reading the logs and know which object/trigger is causing such problems. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-18 : 13:04:43
|
| Can't you put an audit trail trigger on the thable?Either than or use the profiler.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sandeepyadavlko
Starting Member
3 Posts |
Posted - 2011-05-18 : 13:12:16
|
| I tried using trace but was not able to replicate the issue. Actually this issue is very unusual and should not happen. |
|
|
|
sandeepyadavlko
Starting Member
3 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-05-18 : 13:28:32
|
| Did you read Part 2? If the answer is not in either article I don't know what else we can do to help. |
|
|
|
AndrewHogg
Starting Member
1 Post |
Posted - 2011-05-18 : 20:01:54
|
| The log decoding allows you to view the data that was deleted, but as you have seen - it is not trivial to decode. Another issue is that on the log record, only the SPID is recorded for a change, meaning you can not easily trace it to a user.However, that can be set up (assuming people log in on their own accounts or at least run the deletion from their own computer).http://sqlfascination.com/2011/01/08/which-user-made-that-change/In that one, I set up a login trigger and then used this to join to the output from fn_dblog to get the login details to match every change.What I have yet to see, it any information in the log indicating which procedure issued the deletion - only that the row was deleted, you could of course search the procedure cache and try pull out any reference to that table - depending on whether the server is reset / procedure cache came under pressure you may / may not find it.Andrew HoggSQLFascination |
|
|
|
|
|
|
Urgent Need to read transaction logs