Post reply

How to find the last time a table was updated

  • November 5, 2007 at 5:06 pm

    #119139

    Hi Guys,

    Does someone know how I can find the last time a record was inserted into a specific table, deleted from the table or updated ?

    What I mean is: Does MS-SQL keep record of the last time a table was updated? I believe we can find this information in a system table or view, but I couldn't find it yet.

    Thanks a ton,

    Luiz.

  • November 5, 2007 at 6:12 pm

    #748462

    unless you have the database recovery Model property set to Full instead of Simple, there is no way to determine this without adding features yourself.

    if the database is set to Full Recovery mode, you can use a tool to read the transaction log to determine a change on a specific table.

    Otherwise, you will need to add some triggers to every table in the database you want to start tracking, and you'll be able to determine changes from that point on. there's some nice script contributions here on SSC to add an audit trail...search the scripts section for "audit"

    If it is set to Simple, there is no way to determine if any changes occurred, other than comparing a previous database to the current (and there's no timestamp..just a comparison of changes, so you know this row in dbbak.table1 is not the same as the same row in production.table1)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 6, 2007 at 5:30 am

    #748598

    Full Sun (www.Fullsun.com) has a free log reader tool that will help if you have a transaction log.

    Normally I would say you get what you pay for, but in this case, you get slightly more than you pay for.

Viewing 3 posts - 1 through 2 (of 2 total)

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