Update or Insertion in Table

Question

Update or Insertion in Table

faiz_ku

SSC-Addicted

Points: 447
More actions
April 9, 2009 at 11:50 pm

#155545

Can I find out in sqlserver 2005 that in which tables in a database data has been updated or inserted in a certain date and what was the time. Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

SQL Server doesn’t keep this kind of information. If you need to know when data in table was modified, you’ll need to create you own mechanism. You can use profiler or triggers or run all the DML statements through a stored procedure and in it write this information into a log file.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

raj acharya SSCrazy Points: 2805 More actions · Answer 2

yeh man you need to create your own trigger for that.....i don't think so there is no specific mechanism for insertion and update ....but you can put trigger on table insertion and updation

Raj Acharya

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

faiz_ku (4/9/2009)
Can I find out in sqlserver 2005 that in which tables in a database data has been updated or inserted in a certain date and what was the time. Thanks in advance

It's always a best practice to look for an audit table and maintaining one, it doesnt cost much of overhead on the server.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 4

I would be very discreet about setting up dml triggers for audit purposes. I've seen folks take this concept too far and end up impacting performance (due to the overhead) as well as increasing the database size with the ever increasing audit data.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

SA (4/10/2009)
I would be very discreet about setting up dml triggers for audit purposes. I've seen folks take this concept too far and end up impacting performance (due to the overhead) as well as increasing the database size with the ever increasing audit data.

I couldn't agree more.

How about adding two simple columns to your sensitive tables?... timestamp and userid?

Last but not least, it is not entirely true that SQL Server does not keeps track of changes, if you are backing up your TLOGS there are third party products out there that would allow you to mine them looking for whatever you are looking for.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.