Results 1 to 7 of 7
  1. #1
    Join Date
    May 2010
    Posts
    52

    Unanswered: Checking when the the last time a table was up dated

    is there a query i can run to check when a table was last updated. I want to delete tables that are not in use anymore and checking the last time a table was updated will help me determine which tables i should delete. thanks guys

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Exactly what makes you think that lack of updates correlates with lack of use? A table could be heavily involved in read operations with never an update.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2010
    Posts
    52

    i hear you, but

    the tables in the database i am working on have to be active, as in inserted or updated. on a regular base or otherwise it means they are not necessary.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    How high is the traffic on this server and what year is it?

    If it's low traffic, you could run profiler for a while and monitor what entities actually get touched. There is a non-trivial performance hit to run profiler on a production SQL server...

    If it's 2008+ you could look at CDC to capture all modifications on the tables you're interested in. That won't tell you about reads, however. I would expect the performance implications to be less severe than a profiler trace.
    Last edited by Teddy; 04-11-11 at 13:29.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Heck, you could just run a record count against all the tables, then run the same count in a day or two and see which one's changed.
    But of course, the best practice would be to put a datetime column on each table with a trigger to update it whenever a modification occurs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2010
    Posts
    52

    the reason

    Quote Originally Posted by blindman View Post
    Heck, you could just run a record count against all the tables, then run the same count in a day or two and see which one's changed.
    But of course, the best practice would be to put a datetime column on each table with a trigger to update it whenever a modification occurs.
    the reason is i have a database with a bunch of tables, some of the tables are not in use anymore so i wanted to clean the database.....I figured having the trigger thing is the only option, but not going to help me meet my goal. since the tables were created years ago

  7. #7
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    Create an index on the table and use this dynamic management view for an updates on a particular table

    select * from sys.dm_db_index_usage_stats

    NOTE : This view will be flush or truncate you can say if sql server stop or restart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •