August 18, 2009 at 10:37 am
In SQL Server 2000 , is there a way to find out all the past history actions on a particular stored procedure or a table ? Can a transaction log be used to find out all the past actions. just like we can find the recently created object in a database by sorting on crdate desc,
(SELECT * FROM SYSOBJECTS ORDER BY CRDATE DESC)
Is there a way to find out which sp or table was changed and what changes were made ?
August 18, 2009 at 11:34 am
You might be able to get that from the log files. If the database is in full recovery, get a log parser and see what you can find.
Otherwise, this kind of thing is why it's a good idea to set up server-side tracing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 19, 2009 at 1:06 am
The Database is in Simple Recovery Mode. Where will the log files be stored ? Can you paste a pic of the location.
August 19, 2009 at 1:48 am
If the DB is in simple recovery then the transaction log will be getting automatically truncated every minute or so, and there will be no way to get info older than that out.
I suggest that, going forward, you investigate either a server-side trace or DDL triggers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 19, 2009 at 7:04 am
Source control for all scripts run against the database is also a good way to control and track what objects get created/modified/dropped.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply