14 Jun 2013 00:45

Joined:	20/10/2007
Level:	Gold
Points:	1887

Points:	3

How to track history in Sql server R2 database tables?

Are you looking for information on options for history or log which is maintained in sql server 2008? Want to know how to track history in Sql server R2 database tables? Read this thread to learn more about Sql server R2 database examples.

Hi,
I want to know if there is any option to track the history in Sql server R2 database tables if anybody makes any changes to any table values. Actually last night somebody removed some columns from the tables but we are not able to find out who has done it.Is there any option where history or log is maintained in sql server 2008.

#719826

14 Jun 2013 01:09

Joined:	12/11/2009
Level:	Gold
Points:	43848

Points:	2

Hi Reena,

Please execute below query and see if it helps,


SELECT * FROM fn_dblog(NULL, NULL) where Operation = 'LOP_MODIFY_COLUMNS'

You may find all the change details in this table, so based on the requirement you can filter it by adding values in where clause.

Regards,
Asheej T K

#719827

14 Jun 2013 01:09

Joined:	28/06/2007
Level:	Diamond
Points:	56294

Points:	2

Its hard to tracj the user who has modified the code. if there is no policy implemented already.

The thing is SQL profiler and Tracing can help you in identifying the data modified from the tables. Refer MSDN for more details on Tracing and profiler.

Thanks & Regards
Anil Kumar Pandey
Microsoft MVP, DNS MVM

#719828

14 Jun 2013 01:16

Joined:	20/10/2007
Level:	Gold
Points:	1887

Points:	1

Hi Asheej,
I executed this query but I am unable to understand this. Is there any option to get the date information to see all the changed columns in the tables?

Thanks

Reena

#719859

14 Jun 2013 05:00

Joined:	08/10/2009
Level:	Gold
Points:	14808

Points:	2

Reena,

If you have already implemented the audit using Trigger. Then you can find who has deleted the columns.

if you do not have any audit trigger, then you cannot identify who has removed that column from the respective table. Log may help but it could not help if the user has already logged out from the session.

Please mark this as Answer, if this helps

Regards,
Alwyn Duraisingh.M
<< Database Administrator >>
Jesus saves! The rest of us better make backups...

#720039

17 Jun 2013 01:43

Joined:	20/10/2007
Level:	Gold
Points:	1887

Points:	1

Hi Alwyn.
This is really disappointing for me because we don't know if the user responsible for deleting the columns was logged out or not and even I am not sure if we had audit trigger on that database or not as the database location is on a different server.
Anyways, Thanks for the reply.

Thanks

Reena

How to track history in Sql server R2 database tables?

Email subscription

About dotnetspider.com

Quick Links

General

Services