You must Sign In to post a response.
  • Category: SQL Server

    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
    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
    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
    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
    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
    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


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.