December 2, 2009 at 11:55 pm
Hi,
Recently someone (not deliberately coz only 5-10 records were missing from around 40000 records which nobody would do:cool:) deleted some records from a table off my production DB of course I did recover them using a backup. But I would like have some kind of change log stating which user, from which machine (hostname) and when did that insert, update or delete from my DB.
How do I go about it? I thought of a trigger but writing a trigger on more than 2000 tables is a nightmare:hehe:. Does anybody have an idea as to how do I do it on the DB level. Something like a Change Log table where I can get on a day-to-day basis what is getting modified on the database.
Any inputs from you guys would be highly appreciated.
Regards,
Prashant
December 3, 2009 at 12:09 am
Hi
Just a suggestion...
What about a server side trace that tracks delete statements. You can filter out delete statements from application users.
"Keep Trying"
December 3, 2009 at 12:18 am
Not possible without triggers / defaults.
SQL2008, CDC gives some options but not capture hostname.
And CDC is also way too expensive to be placed on production.
Regards,
Raj
December 3, 2009 at 12:44 am
Hi
Even though if we use Trigger is it is possible to get the data who inserted/deleted from which machine(PC) you have to use only SQL no .Net or other thing to find is there a way?
Thanks
Parthi
December 3, 2009 at 12:48 am
Select * from sysprocesses where spid = @@spid
This one help you provide the details about the current connection..
check the results returned and add them to ur trigger..
Regards,
Raj
December 3, 2009 at 7:12 am
Depending on the volatility of the system, you could take a look at the output from the default trace. It should be available on the server with the log & error files. By default there are five files kept, 20mb each, they roll over every so often.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2009 at 2:47 am
Grant
Does the default trace show delete statements?
"Keep Trying"
December 4, 2009 at 6:46 am
ChiragNS (12/4/2009)
GrantDoes the default trace show delete statements?
Nope. Sorry. Stupid statement. It tracks errors & objects created/deleted, that sort of thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply