| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2012-02-20 : 09:41:36
|
| Hi allI appear to have a random piece of code that is deleting data from one of my tables.I've checked all the stored procedures and triggers I can find and there's nothing untoward in there.All the "normal" stored procedures are executed when I want them to be and are passed 2 dates as parameters.This random piece of code appears to be run first thing in a morning and deletes the last 10 days-worth of data.Can anyone point me in the right direction of finding this code so I can stop it running? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-20 : 10:05:59
|
| If you have a time-frame, try SQL Profiler/server-side trace--Gail ShawSQL Server MVP |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-20 : 10:19:26
|
Or you can write a delete trigger which sends you an email with information about time and workstation for the delete?Neither this nor the SQL Profiler approach will stop the delete, but it will find the offending code.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-20 : 11:26:27
|
| for time being write an INSTEAD OF DELETE trigger for table which logs information of process and does not actually do the delete------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-20 : 11:27:02
|
| if its running in morning check sql server agent jobs also for any automated purge jobs------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-21 : 02:10:25
|
quote:
If you have a time-frame, try SQL Profiler/server-side trace
Do you have any idiot-proof instructions on this please?quote:
Or you can write a delete trigger which sends you an email with information about time and workstation for the delete?Neither this nor the SQL Profiler approach will stop the delete, but it will find the offending code.
Never written a trigger before, can you point me in the right direction?quote:
if its running in morning check sql server agent jobs also for any automated purge jobs
Where will I find the agent jobs? (Ignore that, I found them)::edit::I've been keeping a close eye on the table this morning and the data has just disappeared.The only job that was running was called "Load CCC Data".How do I get to the code that the job is running so I can check what is going on? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-21 : 02:53:04
|
1. On which server did the job run?2. Check the steps included in the job.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-21 : 03:06:24
|
| We only have one server (as far as I'm aware) and I know the name of that.How do I check the steps in the job? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-21 : 03:49:46
|
Open the server in Management Studio, scroll down to Agent and expand all jobs.For the correct job, right-click and choose Properties. Then change to Steps tab.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-21 : 03:55:36
|
| Thanks for that. It's definitely not that job (I was looking through the code for that job by coincidence for something else).Where else can I check for that? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-21 : 04:11:59
|
You will need to profile the database or write a trigger.Perhaps it's not a job? Perhaps it's part of an application?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-21 : 04:15:19
|
| Looks like I'll need a trigger then. As I've never written a trigger before, can you point me to some idiot-proof instructions please? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-21 : 05:32:14
|
| If you have a definite time-frame, I would recommend profiler (or server-side trace) rather than a trigger. Check google, lots of good articles.--Gail ShawSQL Server MVP |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-21 : 06:08:21
|
| It's a half hour (or so) timeframe.I'll check what profiler and server-side trace can do.Quick question, will either/both be able to track a delete statement? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-21 : 06:41:35
|
| Yes. (it's not both, It's either or)--Gail ShawSQL Server MVP |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-21 : 07:54:35
|
| OK, thanks for that.I'll set that up ready for tomorrow morning, but I may be back with more questions once it's run. |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2012-02-27 : 05:03:09
|
| Found it(!!)It was a piece of code that did an update on the same table.It was doing a general delete instead of specific items.Now found and sorted. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-02-27 : 05:20:54
|
Great!
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
Finding random code