January 14, 2011 at 6:21 am
Who deleted the records from table?
The answer to this question is to read the log files using DBCC LOG or sys.fn_dblog command. but the question here is how can you identify which records has been deleted. The way is using Operation column (correct me if I am wrong). The AllocUnitName identifies which column has been updated for sepcific entity.
Now my question here is how can i identify who delete the record - UserName?.
Your suggestions are appreciated. Thanks in advance.
Abhijit - http://abhijitmore.wordpress.com
January 14, 2011 at 6:25 am
If you need to know who modified some data (including deletions) then you really have to pro-actively monitor use CDC http://msdn.microsoft.com/en-us/library/bb522489.aspx (or similar).
January 14, 2011 at 6:47 am
Another solution, but harder to maintain and probably not so good for performance - is to set up triggers on tables that contain critical data. If a delete is performed, write some audit information to an audit table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2011 at 8:16 am
The transaction log is not an audit log. It's there for recoverability, integrity and consistency. None of those require knowing login names, host machines or application names of a session that made a change, hence none are included in the log.
If you want auditing of data, check out Change Data Capture, Change Tracking, triggers or SQL Audit and see which fits your requirements.
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
January 16, 2011 at 10:53 pm
Thank you all I did worked earlier on the option suggested but we are confortable with the options. I do have a server side trace running I guess I can utilize it ;-).
Abhijit - http://abhijitmore.wordpress.com
January 20, 2011 at 3:59 am
I think the default trace must be running on your instance. This usualy rolls over after around 20 MB.
If the deletion happened recently, may be you could find what you are looking for. For more details,
you could refer to below link on default trace.
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
M&M
January 20, 2011 at 4:27 am
mohammed moinudheen (1/20/2011)
If the deletion happened recently, may be you could find what you are looking for.
The default trace does not store DML actions (select, insert, update, delete).
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
January 21, 2011 at 1:42 am
Yes Gail, I agree with you.
We could information related to object creation, object deletion etc. but not DML.
M&M
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply