August 10, 2006 at 11:29 am
I inherited a very large SQL server database with a large number of DTS packages and scheduled jobs and absolutely no documentation. I have a table that I know is being updated nightly but I cannot for the life of me find what is updating it. Is there any way to find the package/job which is updating that table without looking through every one individually?
Any help is very, very much appreciated.
Emma
August 11, 2006 at 12:52 pm
Are there audit columns or some sort of other audit mechanism? You can find when the data was updated and then trace that back to any jobs that start around that time...at least it's a place to start.
August 11, 2006 at 6:21 pm
can you run profiler ( or rather server-side traces)?
August 14, 2006 at 5:21 pm
Bill McEvoy recently published an article in SQL Server Magazine where he provided a stored procedure called something like "sp_FindReferences". The procedure accepts the name of a database object (e.g. a table), and then searches the entire database for references to that name.
This might help you find a stored procedure that is being called which updates the table in question.
Elliott
August 15, 2006 at 8:51 am
select name,o.id,xtype, text ,refdate
from sysobjects o
join syscomments c
on o.id = c.id
where text like '%tablename%'
and text like '%update%'
order by name
you could try this to look through the database.
Not sure whether it will find the update if the update is explicitly coded in the DTS package or not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply